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.

 

[Peo Sjoblom]

One way

=INDEX(B1:B2,)*MATCH(TRUE,EXACT(A1:A2,A3),0)

array entered with ctrl + shift & enter

 

[Peo Sjoblom]

Oops! A typo

=INDEX(B1:B2,MATCH(TRUE,EXACT(A1:A2,A3),0))

(although the other formula seems to work as well)