Learn how to create a case-sensitive exact match formula by combining the XLOOKUP and EXACT functions to retrieve data based on text matching.
By default, the XLOOKUP function is not case-sensitive. We will use “Bob” as a lookup value in the example. XLOOKUP returns with “BOB,” which does not meet our criteria.
So, to construct a case-sensitive lookup, you need to use a helper array with logical expression.
Case-sensitive match (XLOOKUP & EXACT)
Before we take a deep dive, here is some 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 typing the EXACT function.
=–EXACT(B3:B13, “Bob”)
The last record in the array = 1, where the name is “Bob”. From now on, we can use the value as the first argument (lookup array) of the XLOOKUP function.
Based on the logic mentioned above, 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 need multiple exact matches? Leverage the power of the FILTER function to retrieve records that are both case-sensitive and non-case-sensitive, ensuring comprehensive data extraction for all variations.
By default, the FILTER function is not case-sensitive. This means it will return BOB and Bob as shown in the output (see the image), regardless of case differences.
To make the search case-sensitive, you must combine the formula with the EXACT function, which checks for case-sensitive matches.