Menu
Does anyone know how to do a case sensitive vlookup with excel?
645
Excel VLOOKUP function is not equipped to handle case-sensitive lookup values. In this above example, no matter what lookup value case is (Matt, MATT, or matt), it'll always return 38 (which is the first matching value).
To do this task, use the IF, FIND, and ISNUMBER functions. Note: The FIND function is case-sensitive.
However, very few people are aware that Excel's VLOOKUP is case-insensitive, meaning it treats lowercase and UPPERCASE letters as the same characters.
VLOOKUP is case-insensitive The VLOOKUP function does not distinguish the letter case and teats lowercase and uppercase characters as identical. Solution: Use VLOOKUP, XLOOKUP or INDEX MATCH in combination with the EXACT function that can match text case.
The key issue to a complete solution here is as Dick Kusleika observes: avoiding stopping the search when a case-insensitive match is found before the case sensitive one.
The Blog entry Case Sensitive Lookup in Excel claims to have a solution to this problem using the INDEX and MATCH functions (original info courtesy a post by Peo Sjoblom). There are some alternatives in the comments too.
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
