XLOOKUP Return Blank If Blank?

Yes, you can make XLOOKUP return a blank cell when the lookup result is blank, but the standard XLOOKUP formula does not do this by default—it returns 0 for an empty cell. The fix is to wrap XLOOKUP in an IF function or use the newer XLOOKUP function’s built-in empty-string handling, depending on your Excel version. This article explains the exact formulas, why the 0 appears, and how to handle blanks in both lookup values and return values.

Why XLOOKUP Shows 0 for Blank Cells

When XLOOKUP finds a match and the cell in the return column is truly empty, Excel’s default behavior is to display a 0. This happens because Excel interprets an empty cell as a numeric zero in many contexts. For example, if you have a price list where some items have no price entered, an XLOOKUP for that item will return 0, which can be misleading in reports or dashboards.

The 0 appears because XLOOKUP is designed to return the value from the return array, and if that value is an empty cell, Excel treats it as 0 in the formula result. This is consistent with how other lookup functions like VLOOKUP and INDEX/MATCH behave, but XLOOKUP’s syntax makes it easier to work around.

The IF Formula to Return Blank Instead of 0

The most straightforward way to return a blank cell when the lookup result is blank is to use the IF function to check if the XLOOKUP result is an empty string. The formula is:

=IF(XLOOKUP(lookup_value, lookup_array, return_array) = "", "", XLOOKUP(lookup_value, lookup_array, return_array))

This formula performs the XLOOKUP twice: once to check if the result is an empty string, and once to return the actual value. If the result is an empty string, it returns a blank cell (represented by “”). Otherwise, it returns the XLOOKUP result.

For example, if you have a product ID in cell A2 and a product table with IDs in column A and prices in column B, the formula would be:

=IF(XLOOKUP(A2, A:A, B:B) = "", "", XLOOKUP(A2, A:A, B:B))

This works because XLOOKUP returns an empty string when the return cell is blank, not a zero. The IF function then converts that empty string to a truly blank cell, which is visually empty and does not affect calculations.

Using LET to Avoid Repeating XLOOKUP

To make the formula more efficient and easier to read, you can use the LET function to define the XLOOKUP result once and then reference it. This avoids performing the lookup twice, which is especially helpful in large spreadsheets. The formula is:

=LET(result, XLOOKUP(lookup_value, lookup_array, return_array), IF(result = "", "", result))

This defines a variable named result that holds the XLOOKUP output, then checks if it is an empty string. If it is, it returns a blank; otherwise, it returns the result. This is cleaner and reduces calculation time.

Handling Blank Lookup Values

Sometimes the lookup value itself is blank, and you may want to return a blank in that case as well. The standard XLOOKUP will return a #N/A error if the lookup value is not found, but if the lookup value is an empty cell, XLOOKUP will treat it as an empty string and try to find it in the lookup array. If there is no match, it returns the not_found value you specify.

To return a blank when the lookup value is blank, you can add an IF condition at the start:

=IF(lookup_value = "", "", XLOOKUP(lookup_value, lookup_array, return_array))

This checks if the lookup value is empty first and returns a blank if so. Otherwise, it performs the XLOOKUP. You can combine this with the previous IF to handle both blank lookup values and blank results:

=IF(lookup_value = "", "", IF(XLOOKUP(lookup_value, lookup_array, return_array) = "", "", XLOOKUP(lookup_value, lookup_array, return_array)))

This ensures that a blank lookup value returns a blank, and a blank result also returns a blank.

Common Problems and Solutions

One common issue is that the formula returns 0 instead of blank because the return cell contains a formula that returns an empty string, not a truly blank cell. In that case, XLOOKUP returns an empty string, and the IF formula above will work. However, if the cell contains a formula that returns 0, the IF check for “” will not catch it, and you will still see 0. To fix that, you can check for both empty string and 0:

=IF(OR(XLOOKUP(...) = "", XLOOKUP(...) = 0), "", XLOOKUP(...))

But be careful: this will also convert legitimate 0 values to blank, which may not be desired. Only use this if you are sure that 0 is not a valid result.

Another problem is that the formula returns #N/A when the lookup value is not found. To handle that, you can use the if_not_found argument of XLOOKUP:

=XLOOKUP(lookup_value, lookup_array, return_array, "")

This returns a blank cell if no match is found. Combine this with the IF formula to also handle blank results:

=IF(XLOOKUP(lookup_value, lookup_array, return_array, "") = "", "", XLOOKUP(lookup_value, lookup_array, return_array, ""))

This is a robust solution that covers both not-found and blank-result cases.

Using the IFNA Function for Not-Found

If you only need to handle the #N/A error (when the lookup value is not found) and want to return a blank, you can use IFNA:

=IFNA(XLOOKUP(lookup_value, lookup_array, return_array), "")

This returns a blank if the lookup value is not found, but it does not handle blank results (which return 0). To handle both, combine IFNA with the IF check:
=IF(IFNA(XLOOKUP(...), "") = "", "", IFNA(XLOOKUP(...), ""))
This is essentially the same as the previous formula but uses IFNA instead of the if_not_found argument.
Frequently Asked Questions
How do I make XLOOKUP return a blank cell instead of 0?
Use the IF formula: =IF(XLOOKUP(...) = "", "", XLOOKUP(...)). This checks if the result is an empty string and returns a blank if so. For better performance, use LET to avoid repeating the lookup.
Why does XLOOKUP return 0 when the cell is blank?
Excel treats empty cells as 0 in numeric contexts. When XLOOKUP returns an empty cell, it is interpreted as 0. This is default behavior and is not a bug.
Can I make XLOOKUP return a blank when the lookup value is blank?
Yes, add an IF condition: =IF(lookup_value = "", "", XLOOKUP(...)). This returns a blank if the lookup value is empty.
How do I return a blank when XLOOKUP finds no match?
Use the if_not_found argument: =XLOOKUP(lookup_value, lookup_array, return_array, ""). This returns a blank cell instead of #N/A.
Does XLOOKUP handle blank cells differently from VLOOKUP?
No, both return 0 for blank cells. However, XLOOKUP's syntax allows you to specify a not-found value, which makes it easier to handle errors.
Can I use XLOOKUP to return a blank for both blank results and not-found values?
Yes, combine the IF check with the if_not_found argument: =IF(XLOOKUP(..., "") = "", "", XLOOKUP(..., "")). This covers both cases.
Related

Export Evernote to OneNote: Step-by-Step Guide
Realme 7 5G: Dimensity 800U, 120Hz, 48MP Cameras
Shopping Search Query Rewrites: How It Works