Combine the LET and IF functions with XLOOKUP, and XLOOKUP will return blank if the lookup result is blank.
If your data is not clean, your data set may contain one or more blank cells. Today, we will show the workaround with XLOOKUP to return blank results if the lookup array contains blank cells.
Generic formula to XLOOKUP return blank if the result is blank
In the picture above, the formula in cell F6 is:
=LET(s, XLOOKUP(C6,products, stock),IF(s="","",s))First, we will look closely at the lookup result in cell C6. The cell is empty, so if you evaluate the XLOOKUP formula, it returns an empty string (“”). On the other hand, the default formula without LET displays as “0”.
Note: “Products” and “stock” are named ranges; they refer to A1:B1 and C1:D1.
Explanation
By default, XLOOKUP returns an #N/A error if no value is available in the lookup array. However, you can manage these cases easily using Excel’s built-in error-handling functions like IFNA or IFERROR.
The basic formula in cell F3 is the following:
=XLOOKUP(E3, products, stock)In the case of general and text-formatted cells, the formula returns “0”.
In the case of date values, you’ll get “1/0/1900”.The problem is that if XLOOKUP returns blank, Excel does not inform the user. Instead, it returns only zero or a wrong date value.
So now we have a dual purpose. First, we will identify the empty lookup result. After that, we will force the XLOOKUP function to display an empty cell due to the formula.
Using a double-lookup method
If you are not familiar with the LET function, you can merge the IF and XLOOKUP functions into a single formula like this:
=IF(XLOOKUP(E6, products ,stock)="","",XLOOKUP(E6, products, stock))Evaluate the formula if the function returns blank: If the result is an empty string, use an empty string. Else, return the XLOOKUP result.
The formula above works fine with VLOOKUP, too, but it doesn’t look very easy. We’ll find a solution to simplify the expression.
XLOOKUP return blank: Workaround with the LET function
With the help of the LET function, we can declare and add a variable to the formula. Using the LET function, we can write the same formula like this:
=LET(s, XLOOKUP(C6,products, stock),IF(s="","",s))
Explanation: First, create a variable named “s” (you can use x,y, or what you want). After that, use the result from XLOOKUP to assign a value to s. If “s” is equal to an empty string, then return an empty string (“”). Otherwise, return the value of “s”.
The revised formula returns the same result but is easily readable.
