Case sensitive vlookup

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".

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

"Tawe" <Tawe.1qkg2b_1118667902.4328@excelforum-nospam.com> wrote in message
news:Tawe.1qkg2b_1118667902.4328@excelforum-nospam.com...
>
> 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".
>
>
> --
> Tawe
> ------------------------------------------------------------------------
> Tawe's Profile:
http://www.excelforum.com/member.php...o&userid=24242
> View this thread: http://www.excelforum.com/showthread...hreadid=378512
>

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 wrote:
>
> 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".
>
> --
> Tawe
> ------------------------------------------------------------------------
> Tawe's Profile: http://www.excelforum.com/member.php...o&userid=24242
> View this thread: http://www.excelforum.com/showthread...hreadid=378512

--

Dave Peterson

Thanks, You've been a great help !!