Learn how to construct a case-sensitive exact match by combining the XLOOKUP and EXACT functions.
The XLOOKUP function is not case-sensitive by default. We will use “Bob” as a lookup value in the example. XLOOKUP returns with “BOB,” which does not meet our criteria.
So, if you want to construct a case-sensitive lookup, you need to use a helper array with logical expression.
Workaround with case-sensitive match (XLOOKUP & EXACT)
Before we take a deep dive, here is a piece of advice:
If you are working with complex formulas, it is worth following a simple rule: evaluate the formula from the inside out!
Formula:
=XLOOKUP(1, –EXACT(B3:B13, “Bob”), B3:H13)
The EXACT function returns an array that contains TRUE or FALSE values using our data set range B3:B13.
If the value is TRUE in the return array, we have the right position for the lookup value, “Bob”. In this example, it is row 13. Using the Boolean logic, we can construct an array that contains 0/1 values. To do that, use a double negative!
Apply two minus (-) signs before starting to type the EXACT function.
–EXACT(B3:B13, “Bob”)
The last record in the array = 1, where the name is “Bob”. From now, we can use the value as a first argument (lookup array) of the XLOOKUP function.
Based on the above-mentioned logic, the simplified formula looks like this:
=XLOOKUP(1, {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1}, B3:B13)
Result:
Extract multiple records using the FILTER function
What if you want to need multiple matches? Use the FILTER function to return the case and non-case-sensitive records.
