Case-sensitive VLOOKUP in Google Sheets

VLOOKUP is case-insensitive, so if you try to find values based on different-case keys, you’re out of luck.

For example:

=VLOOKUP(D2,A$2:B$7,2,FALSE)

There’s an easy fix, using CODE(), Google’s powerful ARRAYFORMULA(), and its array brackets {}.

=VLOOKUP(CODE(D2),{ARRAYFORMULA(code(A$2:A$7)),B$2:B$7},2,FALSE)

CODE converts characters to their numeric equivalent; e.g. CODE(“A”)=64 and CODE(“a”)=97. Instead of looking up by the letter keys, we look up their numeric equivalents.