The tutorial explains how to make Excel VLOOKUP case-sensitive, demonstrates a few other formulas that can look up case in Excel and points out strengths and limitations of each function.
I guess every Excel user knows what function performs a vertical lookup in Excel. Right, VLOOKUP. However, very few people are aware that Excel's VLOOKUP is case-insensitive, meaning it teats lowercase and UPPERCASE characters as identical.
Here's a quick example that demonstrates VLOOKUP's inability to distinguish case. Suppose, if you have "bill" in cell A1 and "Bill" in cell A2, the formula
=VLOOKUP("Bill", A1:A10, 2) will catch "bill" because it comes first in the lookup array, and return a value from cell B1.
Further on in this article, I will show you a way to make VLOOKUP case-sensitive. We will also explore several other functions that can do a case-sensitive match in Excel.
We will start with fairly simple LOOKUP and SUMPRODUCT, which however have a few significant limitations. And then, we will have an in-depth look at a bit more complex INDEX / MATCH formula that works impeccably in all scenarios and on all data sets.
As you already know, a usual Vlookup formula is case-insensitive. However, there is a way to make Excel VLOOKUP case-sensitive. You can force it to match case by adding a helper column to your worksheet, as demonstrated in the following example.
Suppose you have "Item IDs" in column B and you want to pull the item's price and associated comment from columns C and D. The problem is that items IDs include both lowercase and uppercase chars. For example, the values in B4 (001Tvci3u) and B5 (001Tvci3U) differ in the last letter only, "u" and "U", respectively:
As you can guess, a usual Vlookup formula
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE) will fetch $90 that is associated with "001Tvci3u" because it comes before "001Tvci3U" in the lookup array. But this not what you want, right?
To be able to perform a case-sensitive vlookup in Excel, you need to add a helper column and populate it with the below formula (where B is your lookup column):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
The formula parses the lookup value into individual characters, converts each char to its code (e.g. "A" is 65 and "a" is 97), and then concatenates these codes into a unique number string.
After that, you use a simple Vlookup formula that matches case:
The following two things are essential for the correct work of your Vlookup case-sensitive formula:
- The helper column should be the left-most column in the lookup range (table_array argument).
- The lookup value (lookup_value argument) shall be a "character code" rather than a real value.
How to use the CODE formula correctly
The CODE formula that is copied across the helper column implies that all of your lookup values have the same number of characters. If not, you need to know the min and max numbers and add as many IFERROR functions as many chars make the difference between the smallest and largest lookup values.
For example, if the smallest lookup value has 3 chars and largest - 5 chars, you use this formula:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
In the MID function, you specify the following arguments:
- 1st parameter (text) - this is text or a cell reference containing the characters you want to extract (B2 in our case).
- 2nd parameter (start_num) - the position of the first character you want to extract. You enter 1 in the first MID function, 2 in the second and so on.
- 3rd parameter (num_chars) - specifies the number of characters you want to return from text. Since you always want just 1, you enter "1" in all the functions.
VLOOKUP limitations: The VLOOKUP function is not the best way to do a case-sensitive lookup in Excel because, firstly, it requires adding a helper column, and secondly, it works better on homogeneous data sets, or at least when you know the exact number of symbols in your lookup values. If this is not your case, please try out other solutions that follow below.
Excel's LOOKUP function is very akin to VLOOKUP, however its syntax lets you match a case without adding a helper column. To do this, you have to use LOOKUP together with the EXACT function.
If we take the data from the previous example (without the helper column A with codes), the following Lookup / Exact formula will work a treat:
The formula searches through cells A2:A7 for the exact case-sensitive value in cell F2 and returns a value from column B in the same row.
As well as VLOOKUP, the LOOKUP functions works for numerical and text values equally well, and the below screenshot is a good evidence:
Let me briefly explain the use of the EXACT function in the above formula because this is the key point.
Excel's EXACT function compares two text values in the 1st and 2nd arguments and returns TRUE if they are exactly the same, FALSE otherwise. What is really important for us is that EXACT is case-sensitive.
Now, let's analyze how our LOOKUP / EXACT formula works:
- The EXACT function checks the value of cell F2 against all the items in column A (A2:A7) and if an exact case-sensitive match is found, returns TRUE, otherwise - FALSE.
- Since you've entered TRUE in the first parameter (lookup_value) of the LOOKUP function, it pulls a corresponding value from the return column (column B in this example), only when the exact case-sensitive match is found.
Hopefully, the above explanation makes sense and you understand the general idea now. If you do, you won't have any difficulties with other functions that we are going to explore next, since all of them are based on the same principle.
LOOKUP limitations: requires sorting the lookup column in ascending order.
As you've already understood from the heading, SUMPRODUCT is yet another Excel function can do a case-sensitive lookup, but can return numeric values only. If this is not your case, you can skip directly to INDEX MATCH that provides a general solution and works on all data types.
First off, let me briefly explain the syntax of this function, it will help you better understand the case-sensitive SUMPRODUCT formula that follows below.
Excel's SUMPRODUCT multiplies components in the specified arrays, and returns the sum of the products. Its syntax is as follows:
Since we want a case sensitive lookup, we use the EXACT function from the previous example as one of the multipliers:
As you remember, the EXACT function compares the value in cell F2 against all the items in column A and if an exact case-sensitive match is found, returns TRUE, otherwise FALSE . In mathematical operations, Excel treats TRUE as "1" and FALSE as "0", so the SUMPRODUCT function multiplies the returned numbers and sums the products.
Zeros do not count, because a zero always produces "0" whatever number it is multiplied by. So, let's have a closer look at what happens when an exact match is found in column A and "1" is returned. The SUMPRODUCT function multiplies 1 by a number in column B in the same row and returns exactly this number! This is because the products of other multiplications are zeros, which do not impact the returned value in any way.
Regrettably, the SUMPRODUCT function cannot cope with text values and dates since they cannot be multiplied. In this case, you will get the #VALUE! error message like in cell F4 in the screenshot below:
SUMPRODUCT limitations: returns numeric values only.
Finally, you are close to getting a limitation-free case-sensitive lookup formula that works on all data sets.
This example comes last of all not only because the best is saved for the last, but also because the knowledge you've gained from the previous examples may help you understand the case-sensitive MATCH / INDEX formula better.
As you probably know, the combination of INDEX and MATCH functions is used in Excel as a more flexible and powerful alternative to VLOOKUP. The following article does a good job (hopefully : ) explaining how these two functions work together - Using INDEX / MATCH instead of VLOOKUP.
I will just remind you the key points:
- The MATCH function searches for a lookup value in a specified range and returns the relative position of the value, i.e. row or/and column numbers.
- From there, the INDEX function take those numbers and returns a value from the specified column or row, or at the intersection of the column and row if both are supplied.
For INDEX / MATCH to be able to look up a case, you just need to add one more function to the liaison. As you can easily guess, you need the EXACT function again:
In the above formula, the EXACT function works in the same manner as in the LOOKUP formula and produces the same result:
Why INDEX / MATCH is the best solution for case-sensitive vlookup
The main advantages of using INDEX / MATCH are:
- It does not require adding a helper column like the VLOOKUP function does.
- It does not require sorting a lookup column like the LOOKUP function does.
- It works with all data types - numbers, text and dates.
The above mentioned case-sensitive INDEX / MATCH formula seems perfect, right? But, in fact, it is not. Let me show you why.
Suppose, a cell in the return column corresponding to a lookup value is blank. What shall the formula return? Nothing. And now, let's see what it actually returns:
Oops, the formula returns a zero! Maybe, this is no big deal if you are working 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 (VLOOKUP, LOOKUP and SUMPRODUCT) we have 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 need to wrap it in the IF function that will check whether a return cell is blank and return nothing in this case:
In the above formula:
- "B" is the return column
- "1+" is the number that turns a relative position of a 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 the real position of cell A2 in the column is "2", so we add 1 to compensate the difference, for the INDIRECT to return a value from the right cell.
The below screenshots demonstrate the improved case-sensitive INDEX / MATCH formula in action. I've re-written the formula for columns B:D so that the formula bar can fit within the screenshots.
The INDEX / MATCH formula returns nothing if the return cell in empty:
The formula returns "0" if the return cell contains a zero:
If you want the INDEX / MATCH formula to display some message when a return value is blank, you can write something between the last quotes ("") in the formula, like this:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7,MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0))," There is nothing to return, sorry.")
If you want to try out any of the above formulas on your data, feel free to download our case-sensitive vlookup examples.