Excel: How to Case Sensitive Vlookup formula
As you can see, the values in column A can be repeated but with different capitalization. For example, we have ABC all upper case and abc in lower case.
Now what we want to achieve is, to have a case sensitive vlookup formula. In the below table, we want to get the value in cell G2 by looking up the Code (cell F2) in the first table but with taking the case in consideration.
By default, vlookup function will return the first occurrence of abc regardless if it is upper case or lower case. So the result will be 13 and not 1 as we want.
In order to make it case sensitive, we will have to construct a virtual table using the Choose function combined with Exact function.
Exact is a function that makes a case sensitive comparison between two values and returns TRUE if they match or FALSE if they don’t.
Choose function will return a virtual array. We will use it to construct a virtual table of 2 columns:
First column is the result of comparing the value in F2 with each value in the column A using the Exact function so it will return FALSE in all rows except row 7 where it will find an exact match and thus will return TRUE.
Second column will have the values from Column B
The formula to construct this array is CHOOSE({1,2},EXACT(F2,A2:A8),B2:B8)
And here is how that virtual table will look like
Next all what we need to do is to look for the TRUE cell in this virtual table and return the corresponding value. In our case it is 1
Our final Formula would be
=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(F2,A2:A8),B2:B8),2,FALSE)
This still works, thank you so much!
Hi H. Dizon JR,
Ctrl+Shift+Enter is not required anymore since December 2018 as Microsoft has implemented the Dynamic Arrays.
Here is a link from Microsoft
https://support.microsoft.com/en-us/office/dynamic-array-formulas-vs-legacy-cse-array-formulas-ca421f1b-fbb2-4c99-9924-df571bd4f1b4
Thanks for this very informative post you shared.
You just forgot to tell that, since it’s an array of values being looked up, Control+Shift+Enter should be done too, to make the formula return the correct answer.
Leave a comment