The XLOOKUP function in Excel does not perform a case sensitive match by default. So, the values “London”, “LONDON”, and “london” would all result in a match with the XLOOKUP function.
So, how do you perform a case sensitive XLOOKUP formula?
Let’s find out. Read on or watch the video tutorial.
XLOOKUP does not Perform an Exact Match
In this example, the XLOOKUP function is returning the first matching value for the status of each individual and the [Status] in tblRates. The corresponding [Rate] is then returned by the formula.
This XLOOKUP formula is ignoring case when matching the lookup value to the text in the lookup array. This is best demonstrated by the incorrect rate returned for “Sally” in cell D4.
The formula has matched the status of “Sally” to the upper case status value from tblRates. However, we require an exact match on the lower case value found in row 4 of tblRates. The correct rate returned for “Sally” should be 350.
How to Make XLOOKUP Case Sensitive?
To make a case sensitive XLOOKUP formula, we will use the EXACT function for the lookup array argument of XLOOKUP. This function will perform an exact match on the value you are searching for.
The result of the EXACT function will be TRUE or FALSE dependent upon whether an exact match has been made. So, for the lookup value in XLOOKUP, we will enter the value of TRUE. As this will be an exact match for the value returned by EXACT.
What is the EXACT Function?
As its name implies, the EXACT function compares values exactly, including their case. It takes two arguments, which are the two text values to be compared.
=EXACT(text1, text2)IF the two text values match exactly, EXACT returns TRUE, otherwise FALSE is returned.
XLOOKUP Case Sensitive Formula
The following XLOOKUP case sensitive formula returns the correct results.
=XLOOKUP(TRUE,EXACT([@Status],tblRates[Status]),tblRates[Rate])
The rates of 350 and 310 are returned for the lower case status values of “Sally” and “Sophie” respectively. This proves that a case sensitive match was made.
This technique can be applied to the INDEX and MATCH functions in Excel when performing a case sensitive lookup formula also.
Common Mistakes when using a Case Sensitive XLOOKUP
For this case sensitive lookup to work, ensure that the TRUE entered for the lookup value is a logical value, and that it is not entered as a string i.e., “TRUE”. That is a common mistake.
Also, you cannot enter 1 for the lookup value. This will not achieve an exact match on the values returned by the EXACT function, as the function returns TRUE and FALSE values. The familiar #N/A error is returned instead.
The following image shows the array returned by EXACT in the value preview tooltip feature of Excel, and the resulting errors.
You could enter a double unary, or double negative, before the EXACT function to convert the TRUE and FALSE values to 1 and 0 respectively. But, it is probably easier just to enter TRUE for the lookup value.
Wrap Up
This article demonstrated how to use the EXACT function to create a case sensitive lookup with the XLOOKUP function. The ability of the XLOOKUP function to handle arrays is what makes these techniques possible.
Check out the Excel function library to learn all about the best functions in Excel.
Leave a Reply