Case Sensitive v-lookup needed

[Gemi]

Hello,

I am trying to find a case sensitve v-lookup. I used the following vlookup
but found it returned incorrect data because it is not case sensitve.
=VLOOKUP(E2,Sheet2!$A$2:$B$591,2)

Here is an example of my data range and the
Col A(table array) Col B (table array) Col D (v-lookup) Col E
(lookup value)
AK99 AKG THERMAL AIRMITE AF99
ak99 ALKON AIRMITE af99
AN99 ANDERSON ALKON AK99
an99 ALLIED W ALKON ak99
AF10 ANCHOR FLUID PW ALLIED W AN99
af10 AIRMITE PRESSES ALLIED W an99


I tried the following
=IF(EXACT(E2,VLOOKUP(E2,Sheet2!$A$2:$B$591,1,FALSE))=TRUE,VLOOKUP(E2,Sheet2!$A$2:$B$591,2,FALSE),"No exact match") it returned N/A.

Any suggestions?

Thanks in advance for your help.

Lee

 

[Dave Peterson]

First, I think it's a mistake to rely on the upper/lower case this way. I'd use
a different string or another column as an indicator (purely as a way to prevent
user error).

But you could use this array formula:

=INDEX(Sheet2!$B$2:$B$591,MATCH(TRUE,EXACT(e2,Sheet2!$a$2:$a$591),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.)

Adjust the range to match--but you can only use the whole column in xl2007.

You also didn't include False as the 4th parm in your =vlookup() formula. This
seems pretty weird to me. I would think that if you're matching text, you'd
want an exact match (ignoring case):

=VLOOKUP(E2,Sheet2!$A$2:$B$591,2,false)

(The suggested array formula does look for an exact match. That's =match()'s
3rd parm (0).)

 

[Gemi]

I tried the index, did ctrl-shift-enter and verified the {} were there. It
returned a #N/A. When I added the false argument to the end of the vlookup it
returned the #N/A also. The other option I was thinking of - and I am not
sure if it can be done, would be to insert a 1 at the end of all the codes
(af99, akpp, an99) that are lowercase in both the table array and my lookup
value column. It would differnitiate the upper from lower case. Not sure if
there is an easy way to do that other than manually adding a 1 at the end.

Thanks for your help.
Lee

 

[Dave Peterson]

I think adding another character to the value in the cell--both the lookup value
and the table--is a good idea.

But if the =index(match()) array formula didn't work for you, then there is no
match found.

Maybe you have extra characters in that cell (leading/trailing spaces???) that
make the cells different. This problem has to be fixed before you can get
either the array formula working or the =vlookup() formula with the modified
data working.

And maybe you can insert a new column to the left of the table and use a formula
like:

=b1&if(exact(b1,lower(b1)),"_lc","")

It'll append _lc to the string in B1 if it matches a lower case version of the
original string.