XL: How to Perform a Case-Sensitive Lookup

ID: Q142125

The information in this article applies to:

Microsoft Excel for Windows, version 4.0, 5.0, 5.0c
Microsoft Excel for the Macintosh, version 4.0, 5.0, 5.0a
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SUMMARY

You can use lookup functions in Microsoft Excel 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" (without the quotation marks).

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" (without the quotation marks).

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" (without the quotation marks).

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" (without the quotation marks).

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 97 and Microsoft Excel 98 Macintosh Edition

For more information about LOOKUP functions, click the Index tab in Microsoft Excel Help, type

   Lookup worksheet function

and then double-click the selected text to go to the "LOOKUP" topic.

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: 4.0 4.00 5.0 5.0c 5.00 5.00a 5.00c 7.00 97 XL97 8.00 XL98

Keywords          : xlformula 
Version           : MACINTOSH:4.0,5.0,5.0a; WINDOWS:4.0,5.0,5.0c,7.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto kbinfo

Last Reviewed: January 14, 1999