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.

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.

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.

More Excel Function Examples

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

__________________