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.
REFERENCES
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.