5 ways to do case-sensitive VLOOKUP 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)

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

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)

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:

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")

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 and Excel 2021.

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:

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:

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:

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.

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:

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.
   

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

Practice workbook for download

Case-sensitive VLOOKUP examples (.xlsx file)