You are currently offline, waiting for your internet to reconnect

# XL: How to Perform a Case-Sensitive Lookup

This article has been archived. It is offered "as is" and will no longer be updated.
##### SUMMARY
You can use LOOKUP functions in Microsoft Excel 97 and later to compare values to find an exact match, but the match is not case-sensitive. However, you can combine the LOOKUP functions with other built-in functions to perform a case-sensitive lookup.
You can use LOOKUP functions to return data from a list. The functionsuse a LOOKUP value to compare with the list. If a match is found, itreturns data from the list. The data returned comes from the location that is specified in the function.

To force a lookup function to be case-sensitive, combine it with boththe IF and EXACT functions. The following are examples of HLOOKUP, LOOKUP,VLOOKUP, and INDEX-MATCH combined with the IF and EXACT functions to perform case-sensitive searches.

### HLOOKUP

In a new worksheet, type the following data:
`   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")
This formula returns "No exact match" because the lookup value in cellA3 does not use the same case as the entry in the table.

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

### LOOKUP

In a new worksheet, type the following data:
`   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")
This formula returns "No exact match" because the lookup value in cellC1 does not use the same case as the entry in the table.

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

### VLOOKUP

In a new worksheet, type the following data:
`   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")
This formula returns "No exact match" because the lookup value in cellC1 does not use the same case as the entry in the table.

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

### INDEX-MATCH

In a new worksheet, type the following data:
`   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")
This formula returns "No exact match" because the lookup value in cellC1 does not use the same case as the entry in the table.

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

### Microsoft Excel 2000 and Microsoft Excel 2002

For more information about LOOKUP functions, click Microsoft Excel Help on the Help menu, type lookup worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.