Case Sensitive Lookup Formula in Excel

In a lookup table, how can you get Excel to find the item that’s the exact match, including the upper and lower case?

Sample Data to Check

For example, in the lookup table shown below:

  • Cell A4 contains the value,  M7, which has an upper-case M
  • Cell A4 contains the value, m7, which has a lower-case m
lookup table with codes and ID numbers
lookup table with codes and ID numbers

ID Numbers

In column B on the worksheet, there’s a different value for each code that’s entered in column A.

  • So, if I do a lookup for m7, I want the result to be 5, not 4.
  • And if I do a lookup for M7, I want the result to be 4, not 5.

VLOOKUP Is Not Case Sensitive

You could try a VLOOKUP formula, to find the value for m7, but it’s not case sensitive.

In the following screenshot, cell E4 is selected, and its  VLOOKUP formula is visible in the formula bar:

  • =VLOOKUP(D1,$A$1:$B$6,2,0)

The formula refers to cell D1, where I’ve type m7, which has a lower-case m.

The 4th argument of the VLOOKUP is set as 0, which means “find an exact match”

However, the formula result is 4, which is the ID number for the M7 code (upper-case)

VLOOKUP formula not case sensitive
VLOOKUP formula not case sensitive

Microsoft Formula Suggestion

On the Microsoft website, there’s an article that explains how to perform a case sensitive lookup.

One sample formula uses IF and EXACT with VLOOKUP to check the case.

In our sample sheet, Microsoft’s suggested formula is:

  • =IF(EXACT(D1,VLOOKUP(D1,A1:B6,1,FALSE))=TRUE, VLOOKUP(D1,A1:B6,2,FALSE), “No exact match”)

However, this does NOT work in our sample table, because it stops at the M7, and that’s not an exact match for the lookup value m7.

Case Sensitive INDEX MATCH

The Microsoft article has other sample formulas, including an INDEX MATCH, but they all have the same problem — they stop at the M7 above the m7 value.

Fortunately, a search in Google Groups led me to an array formula posted by my old friend, former Excel MVP Peo Sjoblom.

For our table, Peo’s formula would be this array-entered INDEX, MATCH and EXACT combination:

=INDEX(B1:B6,MATCH(1,--EXACT(A1:A6,D1),0))
  • Note: To array-enter this formula, type the formula, then press Ctrl+Shift+Enter. Curly brackets will automatically appear at the start and end of the formula.

Case Sensitive Formula – Correct Result

In the screenshot below, the revised formula is entered in cell E1.

Its formula is visible in the Formula bar, and the correct result of 5, is showing in cell E1.

The formula finds an exact, case-sensitive match for the lookup value, m7, that I typed in cell D1

formula finds an exact, case-sensitive match
formula finds an exact, case-sensitive match

More Excel Function Examples

On the Contextures website you can find more examples of the Excel INDEX function and the Excel MATCH function.
__________________

15 thoughts on “Case Sensitive Lookup Formula in Excel”

  1. Deb,

    The function in the text looks like there is a single minus sign in front of the EXACT() function.

    It may be one of those autocorrect options.

    1. Jason…
      I know you posted this formula back in 2009, but 6 years later, you absolutely saved my life!! I’ve been working on this problem for about 5 hours, with no success, and your formula worked beautifully. Thank you! Thank you! Thank You!

    2. How is that even working? I’m confused.
      And who is Harlan Grove? I can find a lot of works here and there, but can’t find who he is.

  2. Hi Elias,

    thanks a lot buddy.. your Formula assisted me a lot (Y)…

    would be great If you could explain me what is 1/Exact.

  3. I have a stack of work to do that requires matching and deduping on case sensitive URNs, and this is GREAT! Thanks so much for posting.

  4. Ditto. Very helpful. Just realized the Microsoft formula would not work when the same word/string exists in different case forms in the list.

  5. Not pretty, but this works. Convert the SFDC ID to a unique number string, then you can operate as normal. If you have your ID in A1, this will put a new (and case insensitive unique) value in B1
    =CODE(A1)&CODE(MID(A1,2,1))&CODE(MID(A1,3,1))&CODE(MID(A1,4,1))&CODE(MID(A1,5,1))&CODE(MID(A1,6,1))&CODE(MID(A1,7,1))&CODE(MID(A1,8,1))&CODE(MID(A1,9,1))&CODE(MID(A1,10,1))&CODE(MID(A1,11,1))&CODE(MID(A1,12,1))&CODE(MID(A1,13,1))&CODE(MID(A1,14,1))&CODE(MID(A1,15,1))

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.