## Three different ways to do case-sensitive lookup

If you do VLOOKUP, you probably know that one of the limitations of VLOOKUP is case-insensitive.  It means case is not a consideration in the lookup process, where PETER is essentially the same as peter. Hence VLOOKUP will consider that they are the same and return the first matched.  See below demonstration: As you see from above, VLOOKUP always returns “FN” as a result for different “peter”s. Nevertheless, the formula in E3 to E6 manage to perform case-sensitive lookup correctly.  Then the question is HOW?

Let’s take a look at the three different formula:

```=INDEX(\$B\$2:\$B\$6,MATCH(TRUE,EXACT(D2,\$A\$2:\$A\$6),0))
'Ctrl+Shift+Enter

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(D2,\$A\$2:\$A\$6),\$B\$2:\$B\$6),2,FALSE)
'Ctrl+Shift+Enter```
`=LOOKUP(2,1/EXACT(D2,\$A\$2:\$A\$6),\$B\$2:\$B\$6)`

Do you find the common function used in the formula?   Exactly! That’s EXACT.

So before we dive into the formula, let’s take a look at the EXACT function.

Syntax

=EXACT(text1,text2) ‘where you may input text1 directly to the formula with double quotes “”; or reference it to a cell e.g. D2.

### What it does?

Simply compare the two text strings.  Return TRUE for exact match with case being put into consideration (i.e. case sensitive); else FALSE. EXACT(D2,\$A\$2:\$A\$6) compares the content in D2 to the contents in A2:A6.  Say for example the content in D2 is “peter” (this will apply to the rest of the post), then

It compares

• “Amy” with “peter” ==> FALSE;
• “Ben” with  “peter” ==> FALSE;
• “PETER” with “peter” ==> FALSE;
• “peter” with “peter” ==> TRUE;
• “PetER” with “peter” ==> FALSE

and the following array of TRUE/FALSE is returned:

`{FALSE;FALSE;FALSE;TRUE;FALSE}`

It should make sense to the case-sensitive lookup now as what follows is to get the corresponding content of TRUE, in our example, the content in range B2:B6.

Let’s explore the formula one by one:

### 1) Using EXACT, INDEX, MATCH

```=INDEX(\$B\$2:\$B\$6,MATCH(TRUE,EXACT(D2,\$A\$2:\$A\$6),0))
'Confirmed with Ctrl+Shift+Enter; when successfully input, you will see the the formula with {}```

The MATCH function here is to get the position of TRUE, where the case-sensitive match occurs.

`MATCH(TRUE,{FALSE;FALSE;FALSE;TRUE;FALSE},0) 'returns 4`

Then INDEX function returns the corresponding content from B2:B6

`=INDEX(\$B\$2:\$B\$6,4)` which is the forth value in the range B2:B6, which is “Sales”.

### 2) Using VLOOKUP, CHOOSE

```=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(D2,\$A\$2:\$A\$6),\$B\$2:\$B\$6),2,FALSE)
'Confirmed with Ctrl+Shift+Enter; when successfully input, you will see the the formula with {}```

The technique of using CHOOSE({1,2},array1,array2) has been discussed in the blogpost  <Perform VLOOKUP with 2 lookup values> before so I am not going to repeat the details here.  In short, it “constructs” the table_array for VLOOKUP as follow:

`{FALSE,"IT";FALSE,"HR";FALSE,"FN";TRUE,"Sales";FALSE,"AD"}`

which, when “visualized” as a normal range, looks like: Then the VLOOKUP(TRUE…) does the easy job.

```=VLOOKUP(TRUE,{FALSE,"IT";FALSE,"HR";FALSE,"FN";TRUE,"Sales";FALSE,"AD"},2,FALSE)
returns "Sales" as a result
Note: The vlookup value is TRUE, not D2.```

Reminder:  Both formula 1) and 2) are array formula, which you must input by Ctrl+Shift+Enter.

Alright, I heard that you don’t want Ctrl+Shift+Enter… here we go formula 3

3) Using LOOKUP

```=LOOKUP(2,1/EXACT(D2,\$A\$2:\$A\$6),\$B\$2:\$B\$6)
Note: It does not require array input!```

I learned this <Excel Magic Trick 987: LOOKUP Last Number In Column, Excluding Zeroes> from Mike Girvin.  Search “ExcelisFun” in YouTube and you will see thousands of great Excel videos there.

This formula

`=LOOKUP(2,1/EXACT(D2,\$A\$2:\$A\$6),\$B\$2:\$B\$6)`

is a slight modification of the technique described by MIke Girvin.

The tricky part 1/EXACT(D2,\$A\$2:\$A\$6) generates an array consists of either “#DIV/0!” or 1, nothing else.  Hence the lookup value 2 is big enough to get the last (and supposed to be the only) value in the array, i.e. the case-sensitive matched item.

Can’t believe this can be done so easily?  It surely does.

### Note:

If multiple matches found, solution 1) and 2) will always return the first match; while solution 3) will always return the final match.

By the way, I have never encountered a situation when I need “case-sensitive” LOOKUP in workplace.  If you do, please share by leaving a comment.  And also be reminded that Typo regarding is not accepted in “case-sensitive” lookup as it may give you unexpected result. That is exactly the point.  Isn’t it? An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , , , , , . Bookmark the permalink.

### 3 Responses to Three different ways to do case-sensitive lookup

1. Sree hari says:

Just curious why we cant use 1 as the first parameter instead of 2?

Liked by 1 person

• MF says:

Hi Sree,
Indeed that is a good question. If there is no duplicate in your list, it does not matter. Both 1 and 2 would yield the same answer. However, where there are duplicates, 2 would always return the last match; while 1 would give you uncertain answer, depending on the locations of the matched values. Try and experiment.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.