Vlookup Case sensitive

**vinaynaran** · 10-16-2008, 10:18 AM

Dear all,
I need vlookup statment case sensitive - Please help me.
I have excel file like
in Sheet1
a small
A big
w small
W big
Q big
s small

and sheet2 have
a
A
w
W
q
q
s
Q

i want to fill column B (in sheet2) using vlookup with sheet1.

I had used
=IF(EXACT(A1,VLOOKUP(A1,Sheet2!A:B,1,0)),VLOOKUP(A1,Sheet2!A:B,2,0),"No Exact Match")
but it's not working perfect.

Any idea?

Regards
Vinay

**Ron Coderre** · 10-16-2008, 10:33 AM

With
Sheet2 containing this list in A1:B6

Please Login or Register  to view this content.

and...Sheet1 containing this list in A1:A8

Please Login or Register  to view this content.

This formula returns the category (from Sheet2) that corresponds
to the letter in Col_A

Please Login or Register  to view this content.

Copy that formula down through B8.

Is that something you can work with?

**NBVC** · 10-16-2008, 10:38 AM

Try:

Please Login or Register  to view this content.

Which must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

Then copy down.

Note: Adjust ranges to suit. Also, you cannot use whole column references with this formula like A:A unless you are using XL2007

**martindwilson** · 10-16-2008, 10:42 AM

if as in your example its only one letter and you want to return upper and lower case matches use a helper column in front of your data on sheet 1
with =code(b1) dragged down where col b is list of letters in upper/lower
and col c is big,small
then on sheet 2 col b use
=VLOOKUP(CODE(A1),Sheet1!A1:C100,3,FALSE)

**vinaynaran** · 10-16-2008, 11:06 AM

Hi Ron,

It's gives me number like 1,2,3,4..
but in sheet1 output i want like

a small
A big
w small
W big
q No match
q No match
s small
Q big

Thanks

Vinay

**Ron Coderre** · 10-16-2008, 11:10 AM

OOPS! I copied the wrong formula...(sorry)
Try this

Please Login or Register  to view this content.

Actually....this formula is a bit shorter
(and still doesn't involve array entering...C+S+E)

Please Login or Register  to view this content.

Better?

**NBVC** · 10-16-2008, 11:11 AM

Have you tried the other suggestions?

**vinaynaran** · 10-16-2008, 11:18 AM

Hi Ron,

Great. it's working perfect.

Thanks genius.

Vinay

**Ron Coderre** · 10-16-2008, 11:21 AM

You're very welcome, vinaynaran.....I'm glad I could help.

Vlookup Case sensitive

LinkBack

Thread Tools

Rate This Thread

Display