Explanation
WhenXLOOKUPcan’t find a value in a lookup array, it returns an #N/A error.
you’re free to use theIFNA functionorIFERROR functionto trap this error and return a different result.
However, when the result is anempty 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.
The LET function makes it possible to declare named variables in a formula.
If x is empty, then return an empty string ("").
Otherwise, return the value of x.
Each value in the array is processed separately by the IF function.
The variable name “results” is entirely arbitrary.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
More than one condition can be tested by nesting IF functions.
The IF…
LET Function
The Excel LET function lets you define named variables in a formula.
