How do I get XLOOKUP to return blanks if no match found?

General Discussion
1

I want my XLOOKUP formula to return blanks when there is not a match. By default, when XLOOKUP doesn’t find a match it returns #N/A! (XLOOKUP could not find a match). I see I can specify an “if not found” value, but I just want it to return blanks… not a value and not an error message.

2

XLOOKUP lets you set a custom value when a match is not found using the fourth argument in the function, if_not_found, which comes right after the return range.

To return a blank when no match is found with XLOOKUP, simply enter ““ (double quotes) which returns an empty string. In the example below, the formula is =XLOOKUP(B2, ‘State Lookup’!A:A, ‘State Lookup’!B:B, “”)

image
image1224×499 61.6 KB

Here’s more tips for how to use XLOOKUP along with examples.