KB142125 - How to Perform a Case-Sensitive Lookup

Article ID: 142125

Article Last Modified on 8/17/2005

Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0c
Microsoft Excel 4.0 Standard Edition
Microsoft Excel 5.0a for Macintosh
Microsoft Excel 4.0 for Macintosh

This article was previously published under Q142125

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

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

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

Additional query words: 5.0 5.00a 5.00c XL

Keywords: kbhowto kbinfo KB142125