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.

Posted in Uncategorized

3 thoughts on “Case Sensitive Lookup

  1. Couple of downsides to using

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

    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!

    I prefer:

    {=INDEX(A2:B5,MATCH(TRUE,EXACT(A2:A5,”bob”),0),2)}
     


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.