How to Perform a Case-Sensitive Lookup

For a Microsoft Excel 97 and later version of this article, see 214264 .

Summary

You can use lookup functions in Microsoft Excel 7.0 and earlier to compare values to find an exact match, but the match will not be case-sensitive. However, you can combine the lookup functions with other built-in functions to perform a case-sensitive match.

Workaround

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")``
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")``
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")``
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")``
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.

You can use lookup functions to return data from a list. The functions use a lookup value to compare with the list. If a match is found, it will return data from the list. The data returned will come from the location that was specified in the function.

Microsoft Excel 7.0

how do i find an exact match with a lookup

Microsoft Excel 4.0 and 5.0

"User's Guide 2," version 4.0, Chapter 1, "Creating a Lookup Table"

"User's Guide," version 5.0, Chapter 20, "Using Functions to Look Up Values in a List"