Explanation
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:
=XLOOKUP(G5,B5:B16,D5:D16)
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.
Without LET
One way to solve this problem is with the IF function and two XLOOKUP function calls like this:
=IF(XLOOKUP(G5,B5:B16,D5:D16)="","",XLOOKUP(G5,B5:B16,D5:D16))
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 .
With LET
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:
=LET(x,XLOOKUP(G9,B5:B16,D5:D16),IF(x="","",x))
Translation: create a variable named “x” and use the result from XLOOKUP to assign a value to x. If x is empty, then return an empty string (""). Otherwise, return the value of x.
The result is the same but notice this streamlined version of the formula only uses the XLOOKUP function one time.
Multiple values
Because the IF function will process each item in an array separately, you can use the same pattern above to handle multiple results like this:
=LET(results,XLOOKUP(G9,B5:B16,C5:E16),IF(results="","",results))
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.
Explanation
One of XLOOKUP’s features is the ability to lookup and return an entire row or column. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result.
Note: XLOOKUP performs an exact match by default, so match mode is not set.
Working from the inside out, the inner XLOOKUP is used to retrieve all data for “Frantz”:
XLOOKUP(H4,names,data)
XLOOKUP finds “Frantz” in the named range names (B5:B13). Frantz appears in the fifth row, so XLOOKUP returns the fifth row of data (C5:E13). The result is an array representing a single row of data for Frantz, containing 3 months of sales:
{10699,5194,10525} // data for Frantz
This array is returned directly to the outer XLOOKUP as the return_array:
=XLOOKUP(H5,months,{10699,5194,10525})
The outer XLOOKUP finds the value in H5 (“Mar”) inside the named range months (C4:E4). The value “Mar” appears as the third item, so XLOOKUP returns the third item from the sales data, the value 10525.
Without named ranges
The named ranges used in this example are for readability only. Without named ranges, the formula is:
=XLOOKUP(H5,C4:E4,XLOOKUP(H4,B5:B13,C5:E13))
INDEX and MATCH
This example can be solved with INDEX and MATCH like this:
=INDEX(C5:E13,MATCH(H4,B5:B13,0),MATCH(H5,C4:E4,0))
INDEX and MATCH is a good solution to this problem, and probably easier to understand for most people. However, the XLOOKUP version shows off the power and flexibility of XLOOKUP.
Dynamic Array Formulas are available in Office 365 only.
