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.

More Information

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.

References

Microsoft Excel 7.0

For more information about LOOKUP functions, click the Answer Wizard in Help and type
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"


For more information about LOOKUP functions, click the Search button in Help and type:

lookup functions
Lastnosti

ID članka: 142125 – Zadnji pregled: 26. okt. 2013 – Revizija: 1

Povratne informacije