Distinguishing between upper & lower case characters for vlookup .....

[HA]

Hi,

Can someone please help me? .....

Scenario:
Cell A1 contains the character A (upper case)
Cell A2 contains the character a (lower case)
Cell B1 contains 1
Cell B2 contains 2
In cell A3, I want to manually enter either character A (upper case) or
character a (lower case) and then in cell B3 use a VLOOKUP function to
return the value from column B corresponding to the character entered in
cell A3. VLOOKUP function entered is =VLOOKUP(A3,$A$1:$B$2,2,FALSE).

Question:
Using the VLOOKUP function, the value that is returned in cell B3 when A
(upper case) is entered into cell A3 is 1. The same value is returned if the
letter a (lower case) is entered into cell A3 too rather than a value of 2.
Can anyone please recommend a solution for this?

Many thanks,

HA.

 

[Max]

Take a look at Peo Sjoblom's* adaptation of a Harlan Grove* formula in a
previous post
Link : http://tinyurl.com/iksl

*2 of the many excel maestros here <g>

 

[Myrna Larson]

You won't be able to use VLOOKUP, because it's "hard-wired" internally to ignore case. You'll
have to use an array formula such as the one Peo has referred to.

 

[Max]

As the tinyurl link given appears to be down temporarily, here's what Peo
gave previously:
-----------------------------------------------------------------
From: Peo Sjoblom ([email protected])
Subject: Re: How to make VLOOKUP case sensitive?
View: Complete Thread (5 articles)
Original Format
Newsgroups: microsoft.public.excel.worksheet.functions
Date: 2002-07-18 07:11:16 PST

Try this adaptation of a clever Harlan Grove formula

=IF(SUM(EXACT(Sheet1!$B$1:$B$300,$H9)+0)>0,INDEX(Sheet1!$B$1:$D$300,MIN(IF(E
XACT($H9,INDEX(Sheet1!$B$1:$D$300,0,1)),ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$
D$300))))),2),"")

array entered with ctrl+shift&enter

the last 2 in the formula is equivalent of the 2 (column index) in your
formula

I can send you an example via email if you want?