Microsoft KB Archive/142125

= How to Perform a Case-Sensitive Lookup =

Article ID: 142125

Article Last Modified on 8/17/2005

-

APPLIES TO


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



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

-

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

© Microsoft Corporation. All rights reserved.