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,Shee t2!$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 wrote:

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