Case Sensitive Lookup 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?

For example, in this table, row 4 is M7 and row 5 is m7. There’s a different value for each of these in column B. If I do a lookup for m7, I want the result to be 5, not 4.

LUCase01

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 you can see a VLOOKUP formula in the formula bar, and the result of 4 in cell E1.

LUCase02

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, the suggested formula is:

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

However, this doesn’t 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, stopping 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:

=INDEX(B1:B6,MATCH(1,--EXACT(A1:A6,D1),0))

This is an array formula, so type the formula then press Ctrl+Shift+Enter. Curly brackets will automatically appear at the start and end of the formula.

In the screenshot below you can see the formula, and the correct result of 5, in cell E1. The formula finds an exact, case sensitive match for the lookup value.

LUCase04

More Excel Function Examples

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

__________________

You may also like...

15 Responses

  1. Elias says:

    What about this?

    =LOOKUP(1,1/EXACT(A1:A6,D1),B1:B6)

    Regards

  2. Dave says:

    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.

  3. Thanks Elias, that works well too.

    Dave, thanks for pointing out the minus sign problem. I changed the font and hope it looks okay now!

  4. Jason Morin says:

    =LOOKUP(1,1/EXACT(A1:A6,D1),B1:B6)

    That looks an old Harlan Grove technique.

    • Terry Baldwin says:

      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!

    • Kazahk SON says:

      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.

  5. Sharan says:

    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.

  6. Wallond says:

    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.

  7. Sandro says:

    =LOOKUP(TRUE,EXACT(A1:A6,D1),B1:B6)
    This works exactly the same.

  8. Prash says:

    Thank you so much for posting, it helped me a lot.

    Regards,
    Prash

  9. David says:

    Thanks so much for sharing! this saved the day

  10. Simon says:

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

  11. Jim says:

    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 Prash Cancel reply

Your email address will not be published. Required fields are marked *