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

  • Thread starter Thread starter HA
  • Start date Start date
H

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.
 
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.
 
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?
 
Back
Top