vlookup

[leslie]

I am trying to compare two excel worksheets using
vlookup. This is usually no issue except that upper and
lower case letters are mixed with numbers in the data
that is being compared. Does anyone know how to compare
two worksheets and get an exact match with upper and
lower case letters in the mix?

 

[Domenic]

Assuming your lookup table is contained in Columns A and B, and that you
want to lookup a case sensitive value in Column A and return the
corresponding value in Column B, one method would be to use Index/Match:

=INDEX(B1:B5,MATCH(TRUE,EXACT(A10,A1:A5),0))

entered using CTRL+SHIFT+ENTER

where A10 contains your lookup value

Hope this helps!

 

[Biff]

Hi Leslie!

Here's a generic solution:

=IF(SUMPRODUCT(--(ISNUMBER(FIND(A1,F2:F5)))),INDEX
(G2:G5,MATCH(A1,F2:F5,0)),"NO MATCH")

Works just like a VLOOKUP except it's case sensitive.


Biff

 

[Harlan Grove]

Here's a generic solution:

=IF(SUMPRODUCT(--(ISNUMBER(FIND(A1,F2:F5)))),
INDEX(G2:G5,MATCH(A1,F2:F5,0)),"NO MATCH")

Works just like a VLOOKUP except it's case sensitive.

....

FIND can locate partial matches, so FIND("red",Range) could match cells in
Range containing "Fred", "redemption", "bored". If you want whole cell
matches, then you're going to need a more complicated condition.

Also, using MATCH inside INDEX could return incorrect results. If F3 were
the first cell that contained "red" exactly, the MATCH call would return 1
if F2 contained "RED".

I think it requires some convoluted hacking.

IF(SUMPRODUCT((SUBSTITUTE(F2:F5,A1,"")="")*(F2:F5<>"")),
INDEX(G2:G5,MATCH(0,(SUBSTITUTE(F2:F5,A1,"")="")*(F2:F5<>""),0)),
"No match")