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:

Excel Tips - case sensitive lookup.gif

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:



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.


=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.

Excel Tips -Case sensitive lookup1.PNG

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:


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:


'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.


Then INDEX function returns the corresponding content from B2:B6


Excel Tips -Case sensitive lookup1.PNG

which is the forth value in the range B2:B6, which is “Sales”.


'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:


which, when “visualized” as a normal range, looks like:

Excel Tips -Case sensitive lookup2

Then the VLOOKUP(TRUE…) does the easy job.

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

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


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.


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

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.


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

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s