How to Make VLOOKUP Case Sensitive in Excel?

Formula Breakdown

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

• EXACT(G6,B5:B10) -> 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 G6 (where we store our lookup value, john) is in there or not. As we have 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 G6 in every cell in the lookup array. As we got a TRUE 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 is to the rescue!

• MATCH(TRUE,EXACT(G6,B5:B10),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(D5:D10,MATCH(TRUE,EXACT(G6,B5:B10),0)) -> becomes INDEX(D5:D10,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.

 Formula Breakdown

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

• EXACT(H6,$B$5:$B$10) -> Like the 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(H6,$B$5:$B$10)*(ROW($B$5:$B$10) -> 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 B5:B10. Whenever there is a TRUE, it extracts the row number. Otherwise, it is FALSE.

Output: {0;0;0;0;0;10}

• MAX(EXACT(H6,$B$5:$B$10)*(ROW($B$5:$B$10))) -> becomes MAX(0;0;0;0;0;10)

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

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

• VLOOKUP(MAX(EXACT(H6,$B$5:$B$10)*(ROW($B$5:$B$10))),$D$5:$E$10,2,0) -> becomes VLOOKUP(10,$D$5:$E$10,2,0)

Explanation: It can simply extract the lookup value from the array (D5:D10) 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.

 💡  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($B$5:$B$10),$D$5:$D$10) -> If you illustrate this formula by selecting it and pressing F9, it will give you the result as,

Output: {5,100;6,50;7,30;8,80;9,60;10,22}

Explanation: It represents an array that 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(G6,$B$5:$B$10)*(ROW($B$5:$B$10))),CHOOSE({1,2},ROW($B$5:$B$10),$D$5:$D$10),2,0 -> becomes VLOOKUP(10,{5,100;6,50;7,30;8,80;9,60;10,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 functions that we got from the calculation of the above Helper Column discussion.

Output: 22

So, the score of john Show is 22.

Formula Breakdown

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

• EXACT(B5:B10,G6) -> Like the 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(B5:B10,G6) * (D5:D10))) -> 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, that the FALSE values actually cancel out all the other values. The only values that survive are those that are TRUE.

Formula Breakdown

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

• EXACT(G6, B5:B10) -> Like the 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(G6, B5:B10), D5:D10, “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 B5:B10) for the TRUE value and returns a match from the return array (D5:D10).

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.