Case Sensitive Lookup

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:

	A	B
1	Name	Number
2	Bob	one
3	Jim	two
4	bob	three
5	jim	four

The formula =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:

{=INDEX(A2:B5,SUM(EXACT(A2:A5,”bob”)*(ROW(B2:B5)))-1,2)}

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

=INDEX(A2:B5,SUM(EXACT({“Bob”;”Jim”;”bob”;”jim”},”bob”)*(ROW(B2:B5)))-1,2)
=INDEX(A2:B5,SUM({FALSE;FALSE;TRUE;FALSE}*(ROW(B2:B5)))-1,2)
=INDEX(A2:B5,SUM({FALSE;FALSE;TRUE;FALSE}*{2;3;4;5})-1,2)
=INDEX(A2:B5,SUM({0;0;4;0})-1,2)
=INDEX(A2:B5,4-1,2)
=INDEX(A2:B5,3,2)

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:

{=INDEX(A2:B5,MIN(IF(EXACT(A2:A5,”bob”)*(ROW(B2:B5))=0,””,ROW(B2:B5)))-1,2)}

The MIN array formula (that makes up the row argument of INDEX) returns the smallest row, or the first row where “bob” is found.