VLOOKUP Function in Excel

[DannyAuber]

VLOOKUP doesn't distinguish between upper and lower case characters

In other words it sees "DANIEL" and "daniel" as the same.

Does anyone know a workaround for this ?

thanks in advance

dan

 

[koresnordic]

Try this link

http://support.microsoft.com/default.aspx?scid=kb;en-us;142125

Extract below:
VLOOKUP
In a new worksheet, type the following:
A1: NAME B1: AGE C1: joe
A2: Mary B2: 32
A3: Joe B3: 48
A4: Bob B4: 53
A5: Sue B5: 27

In any blank cell on the active worksheet, type the following formula:
=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOKUP(C1,A1:B5,2,FALSE),
"No exact match&quot

The formula above returns "No exact match" because the lookup value in cell C1 is not using the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell C1 to read Joe.

Graham

 

[xlhelp]

This is acopy of solution from Microsoft article Q142125
[tt]
To force a lookup function to be case-sensitive, combine it with both the IF and EXACT functions. Below are examples of HLOOKUP, LOOKUP, VLOOKUP, and INDEX- MATCH combined with these functions to perform case- sensitive searches.

HLOOKUP
In a new worksheet, type the following:
A1: NAME B1: Mary C1: Joe D1: Bob E1: Sue
A2: AGE B2: 32 C2: 48 D2: 53 E2: 27
A3: joe
In any blank cell on the active worksheet, type the following formula:
=IF(EXACT(A3,HLOOKUP(A3,A1:E2,1))=TRUE,HLOOKUP(A3,A1:E2,2),"No exact match&quot
The formula above returns "No exact match" because the lookup value in cell A3 is not using the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell A3 to read Joe.

LOOKUP
In a new worksheet, type the following:
A1: NAME B1: AGE C1: joe
A2: Mary B2: 32
A3: Joe B3: 48
A4: Bob B4: 53
A5: Sue B5: 27
In any blank cell on the active worksheet, type the following formula:
=IF(EXACT(C1,LOOKUP(C1,A1:A5,A1:A5))=TRUE,LOOKUP(C1,A1:A5,B1:B5),"No exact match&quot
The formula above returns "No exact match" because the lookup value in cell C1 is not using the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell C1 to read Joe.

VLOOKUP
In a new worksheet, type the following:
A1: NAME B1: AGE C1: joe
A2: Mary B2: 32
A3: Joe B3: 48
A4: Bob B4: 53
A5: Sue B5: 27
In any blank cell on the active worksheet, type the following formula:
=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOKUP(C1,A1:B5,2,FALSE),"No exact match&quot
The formula above returns "No exact match" because the lookup value in cell C1 is not using the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell C1 to read Joe.

INDEX-MATCH
In a new worksheet, type the following:
A1: NAME B1: AGE C1: joe
A2: Mary B2: 32
A3: Joe B3: 48
A4: Bob B4: 53
A5: Sue B5: 27
In any blank cell on the active worksheet, type the following formula:
=IF(EXACT(C1,INDEX(A1:B5,MATCH(C1,A1:A5,0),1))=TRUE,INDEX(A1:B5,MATCH(C1,A1:A5,0),2),"No exact match&quot
The formula above returns "No exact match" because the lookup value in cell C1 is not using the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell C1 to read Joe. [/tt] "Amicule, num is sum qui mentiar tibi?"

 

[xlbo]

AFAIK, vlookup doesn't distinguish between upper and lower case
You'll probably need a UDF for this tho I'm having a little problem writing one now - got the bones but can't seem to make .FIND wotk in a worksheet udf
Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur