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)