In this tutorial, you will learn how to do a case sensitive VLOOKUP in your Excel sheet.
In a previous tutorial, we already defined the VLookup function. It’s a familiar function to Excel users but most of them ignore that VLOOKUP is case insensitive. By default, the search value in Vlookup function don’t respect the case. It means that « APPLE » and « apple » are treated the same way by VLOOKUP function.
Fortunately, in this tutorial, we will explain to you how to make the VLOOKUP function case-sensitive, and also show you other functions to use as alternatives.
How to Make VLOOKUP case sensitive
Vlookup : Use of a formula instead of support column
Make VLOOKUP case sensitive: Use of a support column
How to perform an exact match search with SOMMEPROD
EQUIV/INDEX – Case sensitive search for all data types
As you already know, a usual VLOOKUP formula is case-insensitive. However, there are techniques to make it the opposite.
In the dataset below, we have a list of student’s names (with different cases) and their mathematical grades:
As you can see, there are three cells with the same name (A2, A4 and A6) but with a different case. On the right (in D3:E5), we have the three names: «Amine», «AMINE» and «amine», as well as their grades in mathematics.
As you can guess, a usual Vlookup formula =VLOOKUP ("AMINE"; $A$2:$B$9; 2; FALSE) will recover 10 which is associated with "Amine" as it comes before "AMINE" in the search array. But that’s not what you want, is it?
In order to perform a case-sensitive VLOOKUP query in Excel, you can use one of the following methods:
Now, we’re going to see how the first method works.
In this method you do not have to worry about inserting a support column. However, you must always have a virtual one! This virtual column is not part of the spreadsheet but is built into the formula.
Here is the formula that will give you the result without the help column:
=RECHERCHEV(MAX(EXACT(D3; $A$2:$A$9) * (LIGNE($A$2:$A$9))); CHOISIR({1.2}; LIGNE($A$2:$A$9); $B$2:$B$9); 2; 0)
Remember that this is a matrix formula, use Ctrl + Shift + Enter instead of only Enter.
How does this formula work?
The formula also uses the help column concept. The difference is that instead of creating the support column yourself in the spreadsheet, consider it as virtual support data that is part of the formula.
Here is the part that works as the support data in the formula:
CHOISIR ({1.2}; LIGNE ($A$2:$A$9); $B$2:$B$9)
To better understand what we mean by virtual support data, have a look on the next screenshot:
In the illustration above, when selecting the “CHOOSE” part of the formula and pressing F9, it shows the result that the “CHOOSE” formula revisits.
The result in this case is: {2.10; 3.15; 4.15; 5.12; 6.14; 7.12; 8.10; 9.17}
This is a table where a point represents the next cell of the same row and a semicolon indicates that the following data is in the next row. Therefore, this formula creates 2 columns of data: one column that contains row numbers and another column contains mathematical notes.
Now, when you use the VLOOKUP function, it simply looks for the search value in the first column (of this 2-column virtual data) and returns the corresponding note. The search value here is a number that we get from the combination of and functions.
You can use a support column to obtain a unique search value for each item in the search table. Indeed, it helps to differentiate names with a different case.
To do this, follow the steps below:
=VLOOKUP (MAX (EXACT(E3; $A$2:$A$9) * (LIGNE($A$2:$A$9))); $B$2:$C$9; 2; 0)
Note: Since this is a matrix formula, use the shortcut Ctrl + Shift + Enter instead of enter tab.
Let’s decompensate the formula to understand how it works:
{VRAI; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX}
{2; 0; 0; 0; 0; 0; 0; 0}.
You can also complete your support column with the form below (Where B is your search column):
=CODE(STXT(A2; 1; 1))&CODE(STXT(A2; 2; 1))&CODE(STXT(A2; 3; 1))&CODE(STXT(A2; 4; 1))&SIERREUR(CODE(STXT(A2; 5; 1)); "")
The above formula analyzes the search value into individual characters, converts each character into its code, (for example, «A» is 65 and «a» is 97), then concatenates these codes into a unique String value:
After completing your support column, you can use a simple VLOOKUP sensitive case formula:
=RECHERCHEV (F3; $B$2:$C$9; 2; FAUX)
The CODE formula that is copied into the support column implies that all your search values have the same number of characters. Otherwise, you need to know the minimum and maximum numbers and add as many SIERREUR functions as the number of characters that differentiate between the smallest and largest search values.
For example, if the smallest search value has 3 characters and the largest one has 5 characters, you will use the following formula:
=CODE(STXT(A2; 1; 1))&CODE(STXT(A2; 2; 1))&CODE(STXT(A2; 3; 1))&SIERREUR(CODE(STXT(A2; 3; 1)); "")&SIERREUR(CODE(STXT(A2; 4; 1)); "")
In the STXT function, you have to specify the following arguments:
Up to now, you know how to do a VLookup sensitive case query using a real support column and a virtual one . If you don’t like using a support column, you can also perform a case-sensitive search without it using the method below.
As you might have understood from the subtitle, SOMMEPROD is another sensitive case Excel function. However, it can only return numerical values.
The SOMMEPROD function multiplies the components in the specified tables in argument and returns the sum of the products. Here is the syntax of the function:
SOMMEPROD (tableau1; tableau2 ; tableau3 ;...)
Since we want to perform a case-sensitive search, we will use the EXACT function of the previous example as one of the multipliers.
The EXACT function works as a search tool. However, it is case sensitive. This function compares two text strings and returns TRUE if they are the same, FALSE otherwise.
Although works with arrays, it does not require the use of matrix formula shortcuts (CTRL + Shift + Enter). You can complete a form by simply pressing “Enter” after typing the form.
Considering the previous example, the formula in E4 is:
=SOMMEPROD ((EXACT ($A$2:$A$9; D4) * ($B$2:$B$9)))
In this case, standard searches such as VLOOKUP and HLOOKUP will ignore the case and return the first match, which is « Amine ». However, the EXACT function will match the exact cases in « tableau_recherche ». The returned result is 14:
How does this formula work?
As you can guess, the function compares the value of cell D4 to all items in column A and if a case-sensitive exact match is found, it returns «», otherwise it returns «». In mathematical operations, Excel treats «» as «1» and «»SOMMEPROD
Do not forget that zeros do not count here, because a zero always produces "0" whatever the number by which it is multiplied.
So, let’s explain what happens when an exact match (case sensitive) is found in column A and "1" is returned: the SOMMEPROD function multiplies 1 by the corresponding number in column B (of the same row) and returns exactly that number! Indeed, the products of the other multiplications are zeros, which will have no effect on the returned value.
Now were are going to move to the last part where we will show you how to do a case sensitive search for all data types .
Finally, in this section we will discuss an unrestricted case-sensitive search formula that works on all types of data.
By default, standard searches with VLOOKUP or INDEX + EQUIV are not case sensitive. The VLOOKUP and EQUIV functions will simply return the first match, ignoring the case.
The EQUIV function searches for a value, specified as an argument, within a specified range (Tableau_recherche) and returns its relative position in the array. Then, this relative position of the search value goes directly to the «no_lig» argument of the INDEX function asking it to return a value from this row. However, if you need to do a case-sensitive search, you can do it with a matrix formula that uses INDEX, EQUIV and the EXACTfunction.
In our example, we will use the following formula:
=INDEX ($B$2:$B$9; EQUIV (VRAI; EXACT (D4; $A$2:$A$9); 0))
This formula is a matrix formula and must be entered with the shortcut Ctrl + Shift + Enter.
Since the EQUIV function alone is not case sensitive, we need a way to check case matching. That’s how we need the EXACT function, but the way we use it is a little unusual, because we have to compare a cell to a range of cells. Let’s explain this formula from the inside out:
{FAUX; FAUX; FAUX; FAUX; VRAI; FAUX; FAUX; FAUX}
EQUIV (VRAI; EXACT (D4; $A$2:$A$9); 0)
Note : This formula Will retrieve both text and numeric values. If you only want to retrieve numbers, you can use a formula based on SOMMEPROD explained above.
Now suppose a cell in the return column corresponding to a search value is empty. What will the formula return? Nothing. And now, let’s see what it actually returns:
As you can see, the formula returns zero. Maybe it’s not a great thing if you only work with textual values. However, if your data set contains numbers and some of them are real zeros, it will cause a problem.
To make the INDEX/EQUIV case-sensitive formula absolutely perfect, you must wrap it in the IF function which will check if a return cell is empty and will return nothing in this.
case:=SI(INDIRECT("B" & (1 + EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0))) <> ""; INDEX($B$2:$B$9; EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0)); "")
In this formula, “B” is the return column. The « 1+ » is used to transform the relative position of a cell returned by the EQUIV function into a real cell address. For example, the search table in our EQUIV function is A2:A9, so the relative position of cell A2 is « 1 » because it is the first cell in the table. But the actual position of cell A2 in the column is « 2», so we add 1 to compensate for the difference, so that the INDIRECT function returns a value of the right cell.
As shown below, the formula returns an empty string if the return cell contains a zero: