XLOOKUP in Excel: What If the Return Cell Is Blank?

If XLOOKUP finds a match whose return cell is truly empty, Excel normally displays 0—not a blank. With lookup keys in A2:A5, return values in B2:B5, and the value to find in E2, use the formula below to show a blank for an empty return cell while preserving real zeroes and displaying “Not found” when there is no match: =IFNA(LET(pos,XMATCH(E2,A2:A5,0),result,INDEX(B2:B5,pos),IF(ISBLANK(INDEX(B2:B5,pos)),"",result)),"Not found").

Why XLOOKUP returns 0 for an empty cell

XLOOKUP searches a lookup array for a value and returns the item at the corresponding position in a return array. If that item is a genuinely empty worksheet cell, Excel commonly evaluates the returned reference as zero. That is why a formula can show 0 even though the source cell appears empty. The lookup has succeeded; the zero is about how Excel represents the blank return cell in this formula result.

For example, suppose A2:A5 contains P-101, P-102, P-103, and P-104. Suppose B2 contains “In stock,” B3 is empty, B4 contains the number 0, and B5 contains “Back order.” With P-102 in E2, XLOOKUP finds the empty cell in B3 and displays 0. With P-103 in E2, it finds an actual numeric zero in B4 and also displays 0. The visible results are alike, even though the source data is different.

With those ranges and E2 as the search value, this basic formula returns the corresponding value; for P-102, the result is 0: =XLOOKUP(E2,A2:A5,B2:B5,"Not found"). XLOOKUP uses exact matching by default, so a match does not require an extra match-mode argument.

What the if_not_found argument does—and does not do

The fourth XLOOKUP argument, often called if_not_found, controls what appears when the search value cannot be found. It does not tell XLOOKUP what to return when a match is found but its corresponding cell is empty. In the example above, “Not found” appears for a key absent from A2:A5, but P-102 still produces 0 because that key exists and its return cell is blank.

If you want unmatched searches to look blank, you can supply an empty text string as the fourth argument. With keys in A2:A5, results in B2:B5, and the search key in E2, this formula returns an empty string for a missing key—but still returns 0 when a matched return cell is truly empty: =XLOOKUP(E2,A2:A5,B2:B5,"").

That distinction matters in reports. A blank for “no record found” and a blank for “record found, but no value entered” can mean different things to a reader. If the difference matters, use separate messages such as “No matching code” and “Value not entered,” or add a status column to the source data rather than making one blank stand for both cases.

Return a blank for a blank match without hiding zeroes

The formula in the opening paragraph finds the position with XMATCH, checks whether the corresponding return cell is genuinely empty with ISBLANK, and then uses INDEX to return either an empty string or the stored value. IFNA handles a missing lookup key. LET gives names to the position and result so the formula is easier to follow and avoids repeating the lookup-position calculation.

With lookup keys in A2:A5, corresponding values in B2:B5, and the key in E2, the formula returns a visually blank result for P-102 if B3 is empty, returns the numeric value 0 for P-103 if B4 contains zero, and returns “Not found” for a key absent from A2:A5: =IFNA(LET(pos,XMATCH(E2,A2:A5,0),result,INDEX(B2:B5,pos),IF(ISBLANK(INDEX(B2:B5,pos)),"",result)),"Not found").

Here, "" means an empty text string, not a cell with no formula or content at all. The formula cell is still occupied by a formula, so ISBLANK applied to that formula cell returns FALSE. For most reports, that is fine: the cell looks empty while real numeric zeroes remain visible. If later formulas need to distinguish a formula-produced empty string from a genuinely empty cell, account for that difference.

XMATCH and LET are available in Excel for Microsoft 365 and Excel 2021 or later. In Microsoft 365 on Windows, enter the formula in the result cell and fill it down as needed. Excel for Mac and Excel for the web also support these functions in compatible editions; menu locations and some interface details can differ, but the formula syntax is the same.

Truly empty cells, formulas returning empty text, and zero

“Blank” can describe more than one thing in Excel, so it helps to check the source cell. A truly empty cell has no value or formula. A cell containing a formula such as one that returns "" looks empty but contains a formula and returns text of zero length. A cell containing 0 has a numeric value, even if its number format hides that value.

Truly empty: XLOOKUP can display 0 when this cell is returned. ISBLANK can identify this source condition.
Formula returning "": The result is empty text, not a truly empty cell. A lookup generally returns an empty-looking result rather than the zero associated with a truly empty source cell.
Numeric zero: This is data. A formula that converts every returned zero to blank can conceal a legitimate result, such as a zero balance or zero quantity.

A common workaround is to test whether XLOOKUP returned 0 and replace it with a blank. That is risky: it cannot reliably express “blank source cell only” when a real zero is also possible. Checking the source cell with ISBLANK, as in the earlier formula, preserves the distinction. If your workbook intentionally treats every zero as missing, a zero-to-blank rule may fit, but make that a documented choice rather than an accidental side effect.

Also consider how downstream calculations behave. An empty string returned by a formula is text, while a true blank is an empty cell; functions and tests may treat those differently. For example, ISBLANK is FALSE for a cell containing a formula, even when that formula displays nothing. Keep that in mind when using the output as input to further formulas, filters, or data checks.

Choose a solution that fits your worksheet

There is no single display choice that suits every workbook. Choose based on what “empty” means to the people using the results and whether a real zero is meaningful.

Keep the standard XLOOKUP result if zero is an acceptable indicator that the matched source cell has no value, or if the source data should be corrected instead.
Return an empty string for a truly empty match if a cleaner report is more important and you still need to show legitimate zeroes. Use the ISBLANK approach.
Use a separate not-found message if users need to know that no lookup key matched. Do not confuse that condition with a matched row whose value is missing.
Fix or label the source data if blank has a business meaning, such as “not assessed.” A status column can distinguish that from zero, not applicable, and no matching record.

Another option is a custom number format that suppresses displayed zeroes, but it affects every zero in the formatted cells, including legitimate ones. It changes appearance rather than the value or the lookup logic. Use it only when hiding all zeroes in that range is intentional; it is not a precise substitute for checking whether the source cell was truly empty.

Common problems and checks

If a formula returns an unexpected result, start with the source and match rather than adding more nested conditions. Confirm that the lookup key exists exactly as expected, that the return range has the same number of rows as the lookup range, and that the corresponding source cell is really empty rather than containing a formula, a space, or a zero. A cell containing a space is not blank.

The result says “Not found”: Check for extra spaces, different number-versus-text types, or a key that is absent. For example, the number 123 and the text “123” can behave differently as lookup values.
The result is 0, but the source looks empty: Select the source cell and inspect the formula bar. It may be genuinely empty, or it may contain a formula or a value hidden by formatting.
A valid zero disappears: Check whether a formula replaces all zero results with "", or whether a custom number format hides zeroes. Use a source-cell blank check if zero is meaningful.
The wrong duplicate is returned: XLOOKUP returns the first match by default. If the lookup key appears more than once, confirm that the first matching row is the intended one.

When copying a lookup formula down, verify that the lookup and return ranges stay aligned. Fixed ranges such as A2:A5 and B2:B5 can shift as a formula is filled down unless they are made absolute, or the data is organized in an Excel table with structured references. A misaligned range can look like a blank-cell problem even when the actual issue is that the formula is looking at the wrong row.

Compatibility and alternatives

XLOOKUP is available in Excel for Microsoft 365 and Excel 2021 or later, as well as later perpetual versions that include it. If a workbook must be opened in an older Excel version that does not support XLOOKUP, that version may show a #NAME? error. Check the recipients’ Excel versions before sharing a workbook that relies on newer functions.

In older Excel versions, INDEX and MATCH can perform a similar exact-match lookup. With keys in A2:A5, results in B2:B5, and a search key in E2, this formula returns the matched result or “Not found”; like a basic XLOOKUP, a genuinely empty matched return cell can appear as 0: =IFNA(INDEX(B2:B5,MATCH(E2,A2:A5,0)),"Not found"). IFNA is available in modern Excel versions; for particularly old installations, an alternative error-handling pattern may be required.

Changing from XLOOKUP to INDEX and MATCH does not by itself solve the empty-return-cell issue. The key decision remains whether to preserve actual zeroes, what to show when no match exists, and whether an empty-looking formula result is acceptable in later calculations. Whichever method you use, test it against at least three cases: a normal value, a genuinely empty matched cell, and a legitimate zero.

Frequently Asked Questions

Why does XLOOKUP show 0 when the source cell is blank?
When a match points to a genuinely empty cell in the return range, Excel commonly evaluates that returned reference as 0. It does not mean the lookup failed; it means a matching row was found.

Does XLOOKUP’s if_not_found argument handle blank return cells?
No. That argument supplies a result only when no lookup match is found. Use a check such as ISBLANK on the matched return cell to handle an empty value in a row that does match.

How do I show blank instead of 0 but keep real zeroes?
Check whether the matched source cell is truly empty and return "" only in that case. Avoid replacing every result equal to zero, because that can hide valid numeric zeroes.

Will XLOOKUP return a blank if the source cell contains a formula returning ""?
It generally returns empty text, which looks blank, rather than the zero associated with a truly empty source cell. The source is not truly empty because it contains a formula, and ISBLANK on that source cell will return FALSE.

Can I hide the zero with cell formatting instead?
Yes, a custom number format can hide displayed zeroes, but it hides all zeroes in the formatted range, including legitimate ones. Use it only when that broad display rule is intended; it does not change the underlying value.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.