XLOOKUP case sensitive

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.

not-case-sensitive-example

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.

exact function

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”)

double negative to case sensitive lookup

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:

xlookup case sensitive result using 1

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.

multiple records with FILTER

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.