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:

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({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:

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

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!

I prefer:

Define cName = Match(True,Exact(SomeCell,Name),0)

=INDEX(Number,cName)

I really like “the progression of the calculation of the formula” you give, especially when it’s an array formula.