Because the lookup result in cell D9 is empty, the final result is an empty string (""). By contrast, a standard XLOOKUP formula in cell H5 returns nothing, which displays as "0-Jan-00".
When XLOOKUP can't find a value in a lookup array, it returns an #N/A error. You can use the IFNA function or IFERROR function to trap this error and return a different result. However, when the result is an empty cell, XLOOKUP does not throw an error. Instead, XLOOKUP returns an empty result, which behaves like a zero. This can make it look like the lookup result has a value even though the original cell is empty.
In this example, the goal is to trap an empty lookup result from the XLOOKUP function and display the result as an empty cell. For example, in the worksheet shown, the formula in cell H5 is:
Because H5 is formatted as a date, and because the result comes from cell D9 (which is empty) the result from XLOOKUP behaves like zero and displays as "0-Jan-00". The goal is to display a blank cell, as seen in cell H9, which contains a modified XLOOKUP formula.
One way to solve this problem is with the IF function and two XLOOKUP function calls like this:
Translation: If the result from XLOOKUP is nothing, then return an empty string (""), otherwise, return the result from XLOOKUP.
The structure of the formula is redundant, since the XLOOKUP function appears twice, but the formula itself will work fine, and the same idea can be used with older functions like VLOOKUP.
One way to eliminate the second instance of XLOOKUP in the formula is to use the LET function. The LET function makes it possible to declare named variables in a formula. With LET, the same formula can be written like this:
The return array in this formula covers three columns, so XLOOKUP will return 3 values in an array. Each value in the array is processed separately by the IF function. The variable name "results" is entirely arbitrary.
In this example, the goal is create a VLOOKUP formula that will return an empty cell when the lookup result is an empty cell. When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or IFERROR...
The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal...
The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.