By default, Excel isnotcase-sensitive and this applies to standard lookup formulas likeVLOOKUP,XLOOKUP, andINDEX and MATCH.
These formulas will simply return thefirstmatch, ignoring case.
We need a way to get Excel to compare case.
TheEXACT functionis perfect for this job, but the way we use it is a little different.
Instead of comparing onetext valueto another, we compare one text value tomanyvalues.
EXACT returns TRUE again for “RED” in row 9 of the table.
Every other result is FALSE, including “Red” in row 3.
This array is returned directly to the XLOOKUP function as thelookup_arrayargument.
Now we need to give XLOOKUP an appropriatelookup_value.
Instead of looking for “RED” (the original lookup value), we provide the value TRUE.
The original values are gone and thus we need to look for TRUE and not “RED”.
Finally, we need to provide areturn_array.
This is the column that contains the values we want as a result.
In this example,return_arrayis the last column in the table, data[Qty].
This array is returned to XLOOKUP as thelookup_array.
Notice that XLOOKUP matches on the first TRUE and not the second and last TRUE.
This is standard behavior for Excel’s lookup functions when there is more than one match.
This array is returned directly to the XLOOKUP function as the lookup array argument.
See below for an example.
Extending the logic
The structure of the logic can be easily extended.
It is not necessary to use the double-negative.
The lookup value remains 1, as in the formula above.
If you’re gonna wanna returnmultipleresults formultiplematches, see theFILTER function.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
