Excel: How to Case Sensitive Vlookup formula

By default, vlookup function is non case sensitive. What if we need to do a case sensitive vlookup operation.
For example, let us use the following table

Excel Case sensitive vlookup

 

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.

 

Excel Case sensitive vlookup

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

 

Excel Case sensitive vlookup

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)

 

Excel Case sensitive vlookup


3 comments


  • Tony Linz

    This still works, thank you so much!


  • Paracon Consultants

    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


  • ORLANDO H. DIZON JR.

    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

This site is protected by hCaptcha and the hCaptcha Privacy Policy and Terms of Service apply.