XLOOKUP Case Sensitivity

By default, XLOOKUP performs a case-insensitive lookup, meaning it finds the first match in a range irrespective of whether it’s uppercase, lowercase or mixed case. To circumvent this, it can be used in conjunction with EXACT.

EXACT checks two text strings to see if they’re the same case. TRUE is returned if they are — FALSE if not.

= EXACT ( text1 , text2 )

Example

A table called tblColours contains a set of colour names alongside corresponding numbers.

For the Case-Insensitive Lookup, a standard XLOOKUP formula retrieves the number of ‘GOLD’ from tblColours. As ‘Gold’ appears before ‘GOLD’, 3 is returned.

The formula is modified for the Case-Sensitive Lookup. The lookup_value becomes TRUE and lookup_array uses EXACT to compare ‘GOLD’ to each value in the Colour column. This evaluates to:

{ FALSE ; FALSE ; FALSE ; FALSE ; FALSE ; TRUE ; FALSE ; FALSE ; FALSE ; FALSE }

The sixth element matches the lookup_value (TRUE), which corresponds to 6 in the return_array:

{ 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 ; 9 ; 10 }