Differentiating Case Sensitivity in a Vlookup
 

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

Differentiating Case Sensitivity in a Vlookup
 

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

Differentiating Case Sensitivity in a Vlookup
 

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

Differentiating Case Sensitivity in a Vlookup
 

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

Differentiating Case Sensitivity in a Vlookup
 

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