5 ways to do case-sensitive VLOOKUP in Excel

The tutorial explains how to make Excel VLOOKUP case-sensitive, demonstrates a few other formulas that distinguish text case, and points out strengths and limitations of each function.

I guess every Excel user knows what function performs a vertical lookup in Excel. Right, it's VLOOKUP. However, very few people are aware that Excel's VLOOKUP is case-insensitive, meaning it treats lowercase and UPPERCASE letters as the same characters.

Here's a quick example that demonstrates VLOOKUP's inability to distinguish text case. Suppose if you have "bill" in cell A2 and "Bill" in A4. The below formula will catch "bill" because it comes first in the lookup array and return a matching value from B2.

=VLOOKUP("Bill", A2:B4, 2, FALSE)
By default, Excel VLOOKUP does not distinguish text case.

Further on in this article, I will show you a way to make VLOOKUP case-sensitive. We will also explore a few other functions that can do a case-sensitive match in Excel.

Case-sensitive VLOOKUP formula

As mentioned above, a usual VLOOKUP formula does not recognize the letter case. However, there is a way to make Excel VLOOKUP case-sensitive, as demonstrated in the below example.

Suppose you have Item IDs in column A and want to pull the item's price and comment from columns B and C. The problem is that IDs include both lowercase and uppercase chars. For example, the values in A4 (001Tvci3u) and A5 (001Tvci3U) differ only in the last character, "u" and "U", respectively.

When looking up "001Tvci3U", a standard VLOOKUP formula outputs $90 that is associated with "001Tvci3u" because it comes before "001Tvci3U" in the lookup array. But this is not what you want, right?

=VLOOKUP(F2, A2:C7, 2, FALSE)
A usual VLOOKUP formula is case-insensitive.

To perform a case-sensitive lookup in Excel, we combine the VLOOKUP, CHOOSE and EXACT functions:

VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(lookup_value, lookup_array), return_array), 2, 0)

This generic formula perfectly works in all situations. You can even look up from right to left, something that a regular VLOOKUP formula is unable to do. Kudos to Pouriya for suggesting this simple and elegant solution!

In our case, the real formulas go as follows.

To pull the price in F3:

=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(F2, A2:A7), B2:B7), 2, FALSE)

To fetch the comment F4:

=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(F2, A2:A7), C2:C7), 2, FALSE)
A case-sensitive VLOOKUP formula

Note. In all Excel versions other than Excel 365, this only works as an array formula, so remember to press Ctrl + Shift + Enter to complete it correctly. In Excel 365, due to support for dynamic arrays, it also works as a regular formula.

How this formula works:

The core part that does the trick is the CHOOSE formula with nested EXACT:

CHOOSE({1,2}, EXACT(F2, A2:A7), C2:C7)

Here, the EXACT function compares the value in F2 against each value in A2:A7 and returns TRUE if they are exactly the same including the letter case, FALSE otherwise:

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

For the index_num argument of CHOOSE, we use the array constant {1,2}. As the result, the function combines the logical values from the above array and the values from C2:C7 into a two-dimensional array like this:

{FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}

The VLOOKUP function takes it from there and searches for the lookup value (which is TRUE) in the 1st column of the 2-dimensional array (represented by the logical values) and returns a match from the 2nd column, which is the price we are looking for:

VLOOKUP(TRUE, {FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}, 2, 0)

Case-sensitive XLOOKUP formula

Microsoft 365 subscribers can do a case-sensitive lookup in Excel with even a simpler formula. As you can guess, I'm talking about a more powerful successor of VLOOKUP - the XLOOKUP function.

Because XLOOKUP operates on lookup and return arrays separately, we do not need the two-dimensional array trick from the previous example. Simply, use EXACT for the lookup_array argument:

XLOOKUP(TRUE, EXACT(lookup_value, lookup_array), return_array, "Not found")

The last argument ("Not found") is optional. It just defines what value to return if no match is found. If you omit it, then a standard #N/A error will be returned in case the formula finds nothing.

For our sample table, these are the case-sensitive XLOOKUP formulas to use.

To get the price in F3:

=XLOOKUP(TRUE, EXACT(F2, A2:A7), B2:B7, "Not found")

To extract the comment F4:

=XLOOKUP(TRUE, EXACT(F2, A2:A7), C2:C7, "Not found")
Case-sensitive XLOOKUP formula

How this formula works:

Like in the previous example, EXACT returns an array of TRUE and FALSE values, where TRUE represents case-sensitive matches. XLOOKUP searches the above array for the TRUE value and returns a match from the return_array. Please note, if there are two or more exactly the same values in the lookup column (including the letter case), the formula will return the first found match.

XLOOKUP limitation: available only in Excel 365.

SUMPRODUCT - case-sensitive lookup to return matching numbers

As you understand from the heading, SUMPRODUCT is yet another Excel function that can do a case-sensitive lookup, but it can return numeric values only. If this is not your case, then jump to the INDEX MATCH example that provides a solution for all data types.

As you probably know, Excel's SUMPRODUCT multiplies components in the specified arrays and returns the sum of the products. Since we want a case sensitive lookup, we use the EXACT function to get the first array:

=SUMPRODUCT((EXACT(A2:A7,F2) * (B2:B7)))

Regrettably, the SUMPRODUCT function cannot return text matches since text values cannot be multiplied. In this case, you will get a #VALUE! error like in cell F4 in the screenshot below:
A case-sensitive SUMPRODUCT formula that looks up text values and returns matching numbers

How this formula works:

Like in the VLOOKUP example, the EXACT function checks the value in F2 against all the values in A2:A7 and returns TRUE for case-sensitive matches, FALSE otherwise:

SUMPRODUCT(({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}*{155;186;90;54;159;28}))

In most formulas, Excel evaluates TRUE to 1 and FALSE to 0. So, when SUMPRODUCT multiplies the elements of the two arrays in the same positions, all non-matches (FALSE) become zeros:

SUMPRODUCT({0;0;0;54;0;0})

As the result, the formula returns a number from column B that corresponds to the exact case-sensitive match in column A.

SUMPRODUCT limitation: can return numeric values only.

INDEX MATCH - case-sensitive lookup for all data types

Finally, we are close to getting a limitation-free case-sensitive lookup formula that works in all Excel versions and on all data sets.

This example comes last not only because the best is saved for the last, but also because the knowledge you've gained in the previous examples may help you understand the case-sensitive MATCH INDEX formula better.

The combination of INDEX and MATCH functions is often used in Excel as a more flexible and versatile alternative to VLOOKUP. The following article does a good job (hopefully :) explaining how these two functions work together - Using INDEX MATCH instead of VLOOKUP.

Here, I will just remind you the key points:

  • The MATCH function searches for the lookup value in the specified lookup array and returns its relative position.
  • The relative position of the lookup value goes directly to the row_num argument of the INDEX function instructing it to return a value from that row.

For the formula to recognize text case, you just need to add one more function to the classic INDEX MATCH combination. Obviously, you need the EXACT function again:

INDEX(return_array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))

The real formula in F3 is:

=INDEX(B2:B7, MATCH(TRUE, EXACT(A2:A7, F2), 0))

In F4, we are using this one:

=INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0))

Please remember that it only works as an array formula in all versions other than Excel 365, so be sure to enter it by press the Ctrl + Shift + Enter keys together. If done correctly, the formula will get enclosed in curly braces like shown in the screenshot below:
A case-sensitive MATCH INDEX formula

How this formula works:

Like in all previous examples, EXACT returns TRUE for each value in A2:A7 that exactly matches the value in F2. Since we use TRUE for the lookup_value of MATCH, it returns a relative position of the exact case-sensitive match, which is precisely what INDEX needs to return a match from B2:B7.

Advanced case-sensitive lookup formula

The above-mentioned INDEX MATCH formula looks perfect, right? But in fact, it is not. Let me show you why.

Suppose a cell in the return column corresponding to the lookup value is blank. What shall the formula return? Nothing. And now, let's see what it actually returns:

=INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0))
The INDEX MATCH formula returns zero instead of a blank cell.

Oops, the formula returns a zero! Maybe, it's not really important when dealing solely with text values. However, if your worksheet contains numbers and some of them are real zeros, this is a problem.

In truth, all other lookup formulas discussed earlier behave in the same way. But now you want an impeccable formula, don't you?

To make the case-sensitive INDEX MATCH formula absolutely perfect, you wrap it in the IF function that checks whether a return cell is blank and returns nothing in this case:

=IF(INDIRECT("C"&(1+MATCH(TRUE,EXACT(A2:A7, F2), 0)))<>"", INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0)), "")

In the above formula:

  • "B" is the return column.
  • "1" is the number that turns a relative position of the cell returned by the MATCH function into a real cell address.

For example, the lookup array in our MATCH function is A2:A7, meaning the relative position of cell A2 is "1", because this is the first cell in the array. But in reality, the lookup array begins in row 2. To compensate the difference, we add 1, so the INDIRECT function will return a value from the right cell.

The below screenshots demonstrate the improved case-sensitive INDEX MATCH formula in action.

If the return cell in empty, the formula outputs nothing (an empty string):
The INDEX MATCH formula returns nothing if the return cell in empty.

If the return cell contains a zero, the formula returns 0:
The INDEX MATCH formula returns 0 if the return cell contains a zero.

If you'd rather display some message when a return cell is blank, replace an empty string ("") in the last argument of IF with some text:

=IF(INDIRECT("C"&(1+MATCH(TRUE, EXACT(A2:A7, F2), 0)))<>"", INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0)), "There is nothing to return, sorry.")
The INDEX MATCH formula displays a message when a return value is blank.

Do case-sensitive VLOOKUP an easy way

The users of our Ultimate Suite for Excel have a special tool that makes looking up in large and complex tables easier and stress-free. The best thing is that Merge Two Tables has a case-sensitive option, and the below example shows it in action.

Suppose you want to pull Qty. from the Lookup table to the Main table based on unique Item ID's:
Look up and merge two tables by case-sensitive match.

What you do is to run the Merge Tables wizard and perform these steps:

  1. Select the main table into which to pull new data.
  2. Select the lookup table where to look for the new data.
  3. Choose one or more key columns (Item ID in our case). And be sure to check the Case-sensitive matching box.
    Case-sensitive matching

  4. The wizard will walk you through the remaining three steps where you specify which columns to update, which columns to add and choose some additional options if needed.

A moment later, you will get the desired result :)
Two tables are merged based on case-sensitive matches.

That's how to lookup in Excel taking into account the text case. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Case-sensitive VLOOKUP examples (.xlsx file)

You may also be interested in