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?

Other topics about vlookup:

1. The basic of vlookup
2. Tips in constructing vlookup
3. vlookup with Match 
4. vlookup options – True or False?
5. Advanced vlookup – Text vs. Number
6. Advanced vlookup – Wildcard Characters “?” and “*”
7. Alternative to vlookup – Index and Match

8. Perform VLOOKUP with 2 lookup values