Case sensitive vlookup

[Tawe]

Excel treats uppercase and lowercase text as equivalent in a vlookup.

Does anyone know how to avoid this, so that a vlookup formula doesn't
treat uppercases and lowercases text as equivalent ?

eg. the vlookup of "a" should give a different result than a vlookup
for "A".

 

[Bob Umlas]

Ctrl/shift/Enter this formula:
=INDEX(B1:B100,MATCH(TRUE,EXACT("YourStringHere",A1:A100),0))
is an equivalent case-sensitive =VLOOKUP("YourStringHere",A1:B100,2,False)
Bob Umlas
Excel MVP

 

[Dave Peterson]

One way:

=INDEX(Sheet2!B1:B10,MATCH(TRUE,EXACT(A1,Sheet2!A1:A10),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

My table was in Sheet2!A1:A10.
My value to look up was in A1 (of sheet1).

You can extend the range, but don't use the whole column.

 

[Tawe]

Thanks, You've been a great help !!