Hi,

Is there any way that a vlookup can work correctly against a case sensitive
list please?

for example, I have a list and range like this:

list range
a 1
A 2
b 3
B 4

I am wanting to pull through the correct number in the range depending on
whether it is upper or lower case?

Thanks in advance

Look in the help index for EXACT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cadders" wrote in message
...
Hi,

Is there any way that a vlookup can work correctly against a case
sensitive
list please?

for example, I have a list and range like this:

list range
a 1
A 2
b 3
B 4

I am wanting to pull through the correct number in the range depending on
whether it is upper or lower case?

Thanks in advance

Hi,

One way with your lookup value in C1

=SUMPRODUCT(--(EXACT(C1,A1:A4)),B1:B4)

Mike

"Cadders" wrote:

Hi,

Is there any way that a vlookup can work correctly against a case sensitive
list please?

for example, I have a list and range like this:

list range
a 1
A 2
b 3
B 4

I am wanting to pull through the correct number in the range depending on
whether it is upper or lower case?

Thanks in advance

Mike...as always, you are just an Excel genius - thank you so much



"Mike H" wrote:

Hi,

One way with your lookup value in C1

=SUMPRODUCT(--(EXACT(C1,A1:A4)),B1:B4)

Mike

"Cadders" wrote:

Hi,

Is there any way that a vlookup can work correctly against a case sensitive
list please?

for example, I have a list and range like this:

list range
a 1
A 2
b 3
B 4

I am wanting to pull through the correct number in the range depending on
whether it is upper or lower case?

Thanks in advance

Assuming that A2:B5 contains the lookup table, and D2 contains the
lookup value, try the following formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...

=INDEX(B2:B5,MATCH(TRUE,EXACT(A2:A5,D2),0))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Cadders wrote:

Hi,

Is there any way that a vlookup can work correctly against a case sensitive
list please?

for example, I have a list and range like this:

list range
a 1
A 2
b 3
B 4

I am wanting to pull through the correct number in the range depending on
whether it is upper or lower case?

Thanks in advance