XL: How to Perform a Case-Sensitive Lookup

• 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

   =IF(EXACT(A3,HLOOKUP(A3,A1:E2,1))=TRUE,HLOOKUP(A3,A1:E2,2),
      "No exact match")

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

   =IF(EXACT(C1,LOOKUP(C1,A1:A5,A1:A5))=TRUE,LOOKUP(C1,A1:A5,B1:B5),
      "No exact match")

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

   =IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOKUP(C1,A1:B5,2,FALSE),
      "No exact match")

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

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

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

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