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 withCtrl+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 withCtrl+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?

### Other topics about vlookup:

- The basic of vlookup
- Tips in constructing vlookup
- vlookup with Match
- vlookup options – True or False?
- Advanced vlookup – Text vs. Number
- Advanced vlookup – Wildcard Characters “?” and “*”
- Alternative to vlookup – Index and Match

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

LikeLiked by 1 person

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.

LikeLike

Pingback: Тъмната страна на Силата – ограниченията на VLOOKUP и как да ги преодоляваме - Excel-DoExcel-Do