Excel tutorial: how to do a case sensitive VLOOKUP

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.

Table of contents

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

How to Make VLOOKUP case sensitive

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:

• Using a formula instead of a support column.
• Use of a support column

Now, we’re going to see how the first method works.

  VLOOKUP : Use of  a formula instead of support column

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.

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.

Make VLOOKUP case sensitive: Use of a support column

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:

1. Insert a support column to the left of the column from which you want to retrieve the data.In the example below, we will insert the support column between the two columns A and B:

1. In the support column, enter the formula =ROW (). It will insert the line number into each cell as shown below:

1. Type the following formula in cell F3 to obtain the result of the case sensitive search::

=VLOOKUP (MAX (EXACT(E3; $A$2:$A$9) * (LIGNE($A$2:$A$9))); $B$2:$C$9; 2; 0)

2. Then, copy and paste the formula in the other cells ( F4 and F5)  :

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:

• EXACT (E3; $A$2:$A$9): This first part of the formula compares the search value in the cell E3 with all the values in the range A2:A9 . Then, it returns a “True” and “False” array where the value “TRUE” is returned when there is an exact match. In this case , where the value in E3 is « Amine », this formula would return the following array :

{VRAI; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX}

• EXACT (E3; $A$2:$A$9) * (LIGNE ($A$2:$A$9): it multiplies the truth table (True and false table) above by the row number A2:A9. Wherever there is TRUE, it gives the line number, otherwise it returns 0. In our case , it returns the following table :

• MAX (EXACT (E3; $A$2:$A$9) * (LIGNE($A$2:$A$9)) :
• This formula return the max value of the numbers in the table above. In this case, it would return 2 (which is the line number where there is an exact match of « Amine »).
• Now we simply use this number as the search value and use the search array as B2:C9 in the VLOOKUP function.
• The following two notes are essential for the proper functioning of your case sensitive VLOOKUP formula:
• The support column must be the leftmost column in the search range (“table_matrice” argument)
• The search value («valeur_cherchée » argument) must be a “character code “rather than a real value.

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:

• The first argument « text »: it a text or a cell reference containing the characters you want to extract (A2 in our case).
• The second argument « no_départ » : it represents the position of the first character you want to extract. You enter 1 in the first STXT function, 2 in the second and so on.
• The last argument « no_car »: it specifies the number of characters you want to return from the text. Since you always want only 1, you should enter “1” in all your functions.

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.

How to perform an exact match search with SOMMEPROD

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 .

EQUIV/INDEX – 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:

• EXACT (D4; $A$2:$A$9): where cell "D4" contains the search value and «$A$2:$A$9» is a reference to the search column (Student). This formula will return an array of “TRUE” and “FALSE” values as follows:

{FAUX; FAUX; FAUX; FAUX; VRAI; FAUX; FAUX; FAUX}

• This table is the result of comparing the value of D4 to each cell in the search column. Wherever we see « TRUE », we know we have an exact match that respects the case.
• Now we need to get the position or, in other words, the line number, of the « TRUE» value in this table. To do this , we use the EQUIV function , looking for « TRUE » :

EQUIV (VRAI; EXACT (D4; $A$2:$A$9); 0)

• It is important to note that the EQUIV function will always return the first match if there are duplicates, so if, for example, it finds that there is another exact match in the column, it will only return the first one.
• After we got the row number of the value that corresponds exactly to the search, we simply need to use the INDEX function to retrieve the value at the right intersection of the row and column. The column number in this case is not mentioned because the named range data includes only the notes column (B2:B9).

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: