Microsoft KB Archive/214264

= XL: How to Perform a Case-Sensitive Lookup =

Article ID: 214264

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q214264



SUMMARY
You can use LOOKUP functions in Microsoft Excel 97 and later to compare values to find an exact match, but the match is not case-sensitive. However, you can combine the LOOKUP functions with other built-in functions to perform a case-sensitive lookup.



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 returns data from the list. The data returned comes from the location that is specified in the function.

To force a lookup function to be case-sensitive, combine it with both the IF and EXACT functions. The following are examples of HLOOKUP, LOOKUP, VLOOKUP, and INDEX-MATCH combined with the IF and EXACT functions to perform case-sensitive searches.

HLOOKUP
In a new worksheet, type the following data:   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")

This formula returns "No exact match" because the lookup value in cell A3 does not use the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell A3 to Joe.

LOOKUP
In a new worksheet, type the following data:   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")

This formula returns "No exact match" because the lookup value in cell C1 does not use the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell C1 to Joe.

VLOOKUP
In a new worksheet, type the following data:   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")

This formula returns "No exact match" because the lookup value in cell C1 does not use the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell C1 to Joe.

INDEX-MATCH
In a new worksheet, type the following data:   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")

This formula returns "No exact match" because the lookup value in cell C1 does not use the same case as the entry in the table.

NOTE: To find a case-sensitive match, change the contents of cell C1 to Joe.



Microsoft Excel 2000 and Microsoft Excel 2002
For more information about LOOKUP functions, click Microsoft Excel Help on the Help menu, type lookup worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

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.

Additional query words: XL2002 XL2000 XL98 XL97 8.00

Keywords: kbhowto KB214264

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.