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.