Doing a case sensitive vlookup

Question

Does anyone know how to do a case sensitive vlookup with excel?

Answer 1

My first thought is to use the EXACT() function to test the result of the vlookup:

=IF(EXACT(VLOOKUP(A2,$C$2:$C$4,1,FALSE),A2),VLOOKUP(A2,$C$2:$C$4,1,FALSE),0)

Where A2 = the value you wish to look up and C2:C4 is the range containing the values to look up.

This would return the result of the vlookup and confirm that it matched the case of the value you wish to look up. It would return the vlookup result if they match case, or 0 otherwise.

EDITED

Actually this answer wouldn't work. If your list contained "Bob" and "bob" it would find the first one and only test that. Sorry, I didn't think of that.

Answer 2

it seems to be a feature of vlookup that case does not matter - so Bob is the same as bob. You could use code() to convert to ASCII and lookup on code. This would make your lookup more complicated, and code() only returns the code for the first item in the string.

Answer 3

If the value you are returning is a number and not text, and if the first column of your lookup_table is unique ('bob' doesn't appear more than once), then you can use an array formula like this

=SUM(EXACT(A3:A6,"bob")*(B3:B6))

Enter with Control+Shift+Enter, not just enter. It sums everything in B3:B6 where A3:A6 is exactly "bob". Since there's only one "bob", it's only summing one cell. Because it uses SUM, returning strings won't work.

If 'bob' appears more than once, it will sum all the values, which you probably don't want.

Update

If the value you are looking up is not a number, you could use an array formula to find the row, then wrap an INDEX function around it. Assume your data is A3:B6 and you want to pull data out of column B based on an exact match in column A.

=INDEX(A3:B6,SUM(EXACT(A3:A6,"bob")*(ROW(B3:B6)))-2,2)

Enter with CSE. The SUM portion returns the row where "bob" is found. You have to subtract 2 from it because the data starts in A3 (subtract one less than the row where the data starts). The INDEX function uses that row and pulls from column B.