How to Make VLOOKUP Case Sensitive in Excel (4 Methods)

The VLOOKUP function is one of Microsoft Excel’s most powerful, flexible, and extremely useful functions to search and retrieve values – either exactly matched values or the closest matched values – by looking up a corresponding value. But the limitation for the VLOOKUP function is, it performs a case-sensitive lookup. It can’t differentiate between upper-case and lower-case letters. This article will show you how to make VLOOKUP case sensitive in Excel.


Download Practice Template

You can download the free practice Excel template from here and practice on your own.


VLOOKUP in Excel

VLOOKUP stands for ‘Vertical Lookup‘. It is a function that makes Excel search for a certain value in a column, in order to return a value from a different column in the same row.

Generic formula:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here,

Arguments Definition
lookup_value The value you are trying to match
table_array The data range that you want to search your value
col_index_num Corresponding column of the lookup_value
range_lookup This is a Boolean value: TRUE or FALSE.
FALSE (or 0) means exact match and TRUE (or 1) means approximate match.

4 Dynamic Methods to Make VLOOKUP Case Sensitive in Excel

Consider the following dataset of students. In that dataset, there are two students who have the same first names but different surnames and obtained a different score.

We want to perform a lookup for the john Show’s score. So, let’s apply the generic VLOOKUP formula to get the result.

=VLOOKUP(G3,B2:D7,3,0)

But as you can see in the picture above, it gave us the result of John Cena’s score instead of the score of john Show. It is because VLOOKUP searches for the lookup value in the array and returns the first value that it gets; it doesn’t handle the case sensitivity of letters.

So, in order to get a case-sensitive VLOOKUP, you need to execute the function differently. And to get that, we have to be a little tricky to get john Show’s score in that cell. We can do that by implementing different functions together to perform a VLOOKUP.

In the next sections, we will go through the combination of the INDEX function and the MATCH function, the combination of the VLOOKUP and the CHOOSE function, the SUMPRODUCT function and run the XLOOKUP function to make a case sensitive VLOOKUP in Excel.


1. Using INDEX, MATCH Function to Develop a Case Sensitive VLOOKUP in Excel

We can get a case-sensitive VLOOKUP by combining the INDEX and MATCH function together.

Generic Formula of the combination of the INDEX and MATCH function is,

=INDEX(data,MATCH(TRUE,EXACT(value,lookup_column),0),column_number)

The steps to get a case sensitive VLOOKUP by implementing the INDEX and MATCH function together are given below,

Steps:

  • Click on the cell that you want to have your result value (in our case, the cell was G4).
  • And write the following formula,
=INDEX(D2:D7,MATCH(TRUE,EXACT(G3,B2:B7),0))

index and match function to make vlookup case sensitive

Now look at the picture above, where you can see that the score of john Show is there, not the score of John Cena.

Formula Breakdown:

Let’s break down the formula to understand how we found out john Show’s score.

  • EXACT(G3,B2:B7) -> The EXACT function in Excel returns TRUE if two strings are exactly the same, and FALSE if two strings don’t match. Here, we are giving the EXACT function an array as a second argument and asking it to find whether the Cell G3 (where we store our lookup value, john) is in there or not. As we gave an array as input, we will get an array of TRUE or FALSE in the output. And the output is stored in Excel’s memory, not in a range

Output: {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

This is the output of comparing the value of G3 in every cell in the lookup array. As we got a TRUE so that means there is an exact match of the lookup value. Now we just need to find out the position (row number) of that TRUE value in the array.

The MATCH function to the rescue!

  • MATCH(TRUE,EXACT(G3,B2:B7),0) -> become MATCH({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE})

Explanation: The MATCH function returns the position of the first matched value. In this example, we wanted to get an exact match so we set the third argument as 0 (TRUE).

Output: 6

  • INDEX(D2:D7,MATCH(TRUE,EXACT(G3,B2:B7),0)) -> becomes INDEX(D2:D7,6)

Explanation: The INDEX function takes two arguments and returns a specific value in a one-dimensional range. As we already know the position of the row number (6) that holds our desired value, we are going to use INDEX to extract the value of that position.

Output: 22

So, the score of john Show is 22.


2. Combining the VLOOKUP & CHOOSE Function to Perform a Case Sensitive VLOOKUP in Excel

We can implement two ways in the combination of the VLOOKUP and the CHOOSE function to make a case-sensitive VLOOKUP in Excel.

2.1 Making VLOOKUP Case Sensitive With Helper Column

By inserting a new column to get a unique lookup value for each item in the lookup array is another effective way to get the job done. This helps in differentiating between names with different letter cases. And we are going to name that newly inserted column as the Helper column.

The steps to get a case sensitive VLOOKUP with Helper Column are given below,

Steps:

  • Insert a helper column to the left of the column from where you want to fetch the data.

inserting helper column to make vlookup case sensitive

  • In the helper column, enter the formula =ROW(). It will insert the row number in each cell.
  • Click on the cell that you want to have your result value (in our case, the cell was H4).
  • And write the following formula,
=VLOOKUP(MAX(EXACT(H3,$B$2:$B$7)*(ROW($B$2:$B$7))),$D$2:$E$7,2,0)

making vlookup case sensitive with helper column

Now look at the picture above, where you can see that the score of john Show is there, not the score of John Cena.

Formula Breakdown:

Let’s break down the formula to understand how we found out john Show’s score.

  • EXACT(H3,$B$2:$B$7) -> Like previous discussion, EXACT returns an array of TRUE and FALSE values, where TRUE represents case-sensitive matches and FALSE represents the unmatched values. So, in our case, it will return the following array,

Output: {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

  • EXACT(H3,$B$2:$B$7)*(ROW($B$2:$B$7) -> becomes {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE} * {John,Roman,Seth,Dean,Finn,john}

Explanation: It represents the multiplication between the array of TRUE/FALSE and the row number of B2:B7. Whenever there is a TRUE, it extracts the row number. Otherwise, it is FALSE.

Output: {0;0;0;0;0;7}

  • MAX(EXACT(H3,$B$2:$B$7)*(ROW($B$2:$B$7))) -> becomes MAX(0;0;0;0;0;7)

Explanation: It will return the maximum value from the array of numbers.

Output: 7 (which is also the row number where there is an exact match).

  • VLOOKUP(MAX(EXACT(H3,$B$2:$B$7)*(ROW($B$2:$B$7))),$D$2:$E$7,2,0) -> becomes VLOOKUP(7,$D$2:$E$7,2,0)

Explanation: It can simply extract the lookup value from the array (D2:D7) and as we want to find an exact match so set the argument 0 (TRUE).

Output: 22

So, the score of john Show is 22.

Note: You can insert the helper column anywhere in the dataset. Just make sure to insert it to the left of the column from where you want to fetch the data. You need to then adjust the column number in the VLOOKUP function accordingly.


2.2 Making VLOOKUP Case Sensitive With Virtual Helper data

The idea of using Virtual Helper Data is almost similar to the insertion of the Helper Column, but the twist here is, instead of putting an actual column in the worksheet, the formula itself works as columns.

The steps to get a case sensitive VLOOKUP with Virtual Helper Data are given below,

Steps:

  • Click on the cell that you want to have your result value (in our case, the cell was I4).
  • And write the following formula,
=VLOOKUP(MAX(EXACT(I3,$D$2:$D$7)*(ROW($D$2:$D$7))),CHOOSE({1,2},ROW($D$2:$D$7),$F$2:$F$7),2,0

making vlookup case sensitive with helper data

Now look at the picture above where you can see that the score of john Show is there, not the score of John Cena.

The following part of the full formula works here as the helper data,

=---CHOOSE({1,2},ROW($D$2:$D$7),$F$2:$F$7)---

Formula Breakdown:

Let’s break down the formula to understand how Virtual Helper Data helped in finding john Show’s score.

  • CHOOSE({1,2},ROW($D$2:$D$7),$F$2:$F$7) -> If you illustrate this formula by selecting it and pressing F9, it will give you the result as,

Output: {2,100;3,50;4,30;5,80;6,60;7,22}

Explanation: It represents an array which shows us the row number and the value associated with it from the given array divided by comma (,). And each semicolon (;) represents the new row number following it. So as it seems like, it created two columns consisting of row number and the column which has the return lookup value (i.e. row number and Score column in our case).

  • VLOOKUP(MAX(EXACT(I3,$D$2:$D$7)*(ROW($D$2:$D$7))),CHOOSE({1,2},ROW($D$2:$D$7),$F$2:$F$7),2,0 -> becomes VLOOKUP(7,{2,100;3,50;4,30;5,80;6,60;7,22},2,0)

Explanation: When you apply the VLOOKUP function, it simply looks for the lookup value in the first column from the two virtual data columns and returns the corresponding value (i.e. Score). The lookup value here is the combination of the MAX and EXACT function that we got from the calculation of the above Helper Column discussion.

Output: 22

So, the score of john Show is 22.


3. Using SUMPRODUCT Function to Make VLOOKUP Case Sensitive in Excel

We can get a case sensitive VLOOKUP by implementing the SUMPRODUCT function in Excel.

Generic Formula:

=SUMPRODUCT(- -( EXACT(value,lookup_column)),result_column)

The steps to get a case sensitive VLOOKUP by implementing the SUMPRODUCT function are given below,

Steps:

  • Click on the cell that you want to have your result value (in our case, the cell was G4).
  • And write the following formula,
=SUMPRODUCT((EXACT(B2:B7,G3) * (D2:D7)))

sumproduct to make vlookup case sensitive

Now, look at the picture above where you can see that the score of john Show is there, not the score of John Cena.

Formula Breakdown:

Let’s break down the formula to understand how we found out john Show’s score.

  • EXACT(B2:B7,G3) -> Like previous discussion, EXACT returns an array of TRUE and FALSE values, where TRUE represents case-sensitive matches and FALSE represents the unmatched values. So, in our case, it will return the following array,

Output: {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

  • SUMPRODUCT((EXACT(B2:B7,G3) * (D2:D7))) -> become SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE} * {100,50,30,80,60,22})

Explanation: SUMPRODUCT then simply multiplies the values in each array together to extract a final array, {FALSE;FALSE;FALSE;FALSE;FALSE;22}. And then sum and return the value.

Output: 22

So, the score of john Show is 22.

The magic of this formula is, the FALSE values are actually canceling out all the other values. The only values that survives are those that were TRUE.

So keep in mind that, if there are multiple matches in the array, then SUMPRODUCT will return the sum of all those matched values. Also, SUMPRODUCT works only with numeric values, it doesn’t work with text. So, if you want to get a unique text value, then utilize the above methods that we have discussed.


4. Case Sensitive XLOOKUP Formula to Perform a Case Sensitive VLOOKUP in Excel

We can get a case sensitive VLOOKUP by performing the XLOOKUP function in Excel.

Generic Formula:

=XLOOKUP(TRUE,EXACT(lookup_value, lookup_array), return_array, “Not Found”)

The steps to get a case sensitive VLOOKUP by implementing the XLOOKUP Formula are given below,

Steps:

  • Click on the cell that you want to have your result value (in our case, the cell was G4).
  • And write the following formula,
=XLOOKUP(TRUE, EXACT(G3, B2:B7), D2:D7, "Not found")

xlookup to make vlookup case sensitive

Now look at the picture above, where you can see that the score of john Show is there, not the score of John Cena.

Formula Breakdown:

Let’s break down the formula to understand how we found out john Show’s score.

  • EXACT(G3, B2:B7) -> Like previous discussion, EXACT returns an array of TRUE and FALSE values, where TRUE represents case-sensitive matches and FALSE represents the unmatched values. So, in our case it will return the following array,

Output: {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

  • XLOOKUP(TRUE, EXACT(G3, B2:B7), D2:D7, “Not found”) -> becomes XLOOKUP({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}, {100,50,30,80,60,22}, “Not found”)

Explanation: Then XLOOKUP searches the given array (in our case, the array was B2:B7) for the TRUE value and returns a match from the return array (D2:D7).

Output: 22

So, the score of john Show is 22.

Remember that, if there are multiple same values in the lookup column (including the letter case), the formula will return the first found match.

Note: This XLOOKUP formula will only work in Excel 365.


Key Points You Must Keep in Mind

  • As the range of the data table array to search for the value is fixed, don’t forget to put the dollar ($) sign in front of the cell reference number of the array table.
  • When working with array values, don’t forget to press Ctrl + Shift + Enter on your keyboard while extracting results. Pressing only Enter doesn’t work while working with array values.
  • After pressing Ctrl + Shift + Enter, you will notice that the formula bar enclosed the formula in curly braces {}, declaring it as an array formula. Don’t type those brackets {} yourself, Excel automatically does this for you.

Conclusion

This article explained in detail how to make VLOOKUP case sensitive in Excel by implementing combination of functions. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.


You May Also Like To Explore

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo