Sign up to hear about free Excel training.
I won't share your email with anyone.
2 mins read
Normal look ups in Excel are not case sensitive. To make them case-sensitive you need to use the EXACT function.
In the image below you can see that the formula in cell E3 Is returning the value for the uppercase B not the lowercase b.
The formulas in cells E4 and E5 are working as expected and they are using the EXACT function.
The EXACT function is case-sensitive. It returns TRUE if the values are identical and FALSE if they’re not.
Because we’ve used a range as the first argument each of the cells is compared to the value in the second argument and this returns a list of TRUE and FALSE results. The XLOOKUP is looking for TRUE and it finds the first match.
In the image below we can see the results of the EXACT function in cell E5 – comparing the lowercase b to the list in column A.
The TRUE entry is the second last which is the match for the lowercase b in the list in column A.
If there were more than one TRUE result the XLOOKUP would find the first TRUE.