The VLOOKUP function (and its brothers MATCH, LOOKUP, and HLOOKUP) do not care about the case of the lookup value. Take this data, for example:
=VLOOKUP("bob",A2:B5,2,FALSE) will return “one”. It finds “Bob” and quits looking because it considers that a match.
If you need to find the true “bob”, you can use this array formula:
This is an INDEX function INDEX(array, row, column). The row argument is a SUM array function that returns the row where “bob” is found. You have to subtract 1 from that result because the data starts in A2. Here’s the progression of the calculation of the formula
The added benefit over VLOOKUP, beside case sensitivity, is that you can lookup on and return any column. With VLOOKUP, you can only lookup on the first column. If you have more than one “bob” in your list, it won’t work. In that case, you can get the first occurence of “bob” using this formula:
The MIN array formula (that makes up the row argument of INDEX) returns the smallest row, or the first row where “bob” is found.
3 thoughts on “Case Sensitive Lookup”
Couple of downsides to using
as an analogue to VLOOKUP():
a) If “bob” doesn’t exist, VLOOKUP() gives an #N/A error which can be trapped by ISNA(), the above gives #VALUE!
b) As you point out, if multiple “bob”s exist, VLOOKUP() returns the first, the above gives #REF!
Define cName = Match(True,Exact(SomeCell,Name),0)
I really like “the progression of the calculation of the formula” you give, especially when it’s an array formula.
Posting code? Use <pre> tags for VBA and <code> tags for inline.