Saturday, July 11, 2009

Excel Case Sensitive Lookup

In Excel, the various lookup functions are not case sensitive so you can't differentiate between Joe and joe. Once the lookup locates the first match it references to that occurrence. There is a work around to locate the exact case sensitive match.

Using a Conditional test and the Exact function can differentiate between “Joe” or “joe” as explained in Microsoft support case: http://support.microsoft.com/kb/214264.

However, it will always test the first “Joe” available so you won’t be able to get to the next one.
Therefore, you will need to use the Match function and calculate a narrower range to search where the first insensitive case match has been excluded.

This solution applies to two identical insensitive rows, so if you expect more than that you will need to add more nested ifs to further narrow down the lookup ranges. You also need to specifically address the last row in the range or add a fictitious entry that has no match.

The solution has been tested on 2003 and 2007 versions.

In I1:J4 enter the lookup table

aB Abraham
Ab Aaron
Cd Daniel
Rs Kevin

In A1:C1 enter the titles:
Key Name Range
In A2 A6 enter the lookup values:
cd
Ab
aB
Cd
Ed
Enter the following formulas into B2:B6 and C2:C6
  • For clarity purposes, the Range Column C2:C6 shows the calculated range based on whether there is no match at all, there is a perfect match, or a narrower range is needed for an imperfect match. The formula is:
    =IF(ISERROR(VLOOKUP(A2,I$1:J$4,2,FALSE)),"Missing",IF(EXACT(A2,VLOOKUP(A2,I$1:J$4,1,FALSE)),"I"&(MATCH(A2,I$1:I$4,0))&":J$4","I"&IF(MATCH(A2,I$1:I$4,0)>=4,"4:J$4",(MATCH(A2,I$1:I$4,0)+1)&":J$4")))
  • The Name column uses the Indirect function on the calculated range to lookup the correct range to get the name.
    =IF(ISERROR(VLOOKUP(A2,INDIRECT(C2),2,FALSE)),"Missing",VLOOKUP(A2,INDIRECT(C2),2,FALSE))

The result after applying the formulas:
Key Name Range
cd Missing I4:J$4
Ab Aaron I2:J$4
aB Abraham I1:J$3
Cd Daniel I3:J$3
Ed Missing Missing
Of course, you can get rid of column C and incorporate its formula within B.

No comments:

Post a Comment

Enter your comment here