Case-sensitive lookup

You may know that you can combine the INDEX and MATCH functions, or use the VLOOKUP function to lookup values in Excel. However, the lookups aren’t case-sensitive. So, in order to perform a case-sensitive match, you should take advantage of the EXACT and CHOOSE functions.

Perform a case-sensitive lookup with INDEX and MATCH
Perform a case-sensitive lookup with VLOOKUP

Perform a case-sensitive lookup with INDEX and MATCH

To know the grade of YUKI as shown in the screenshot above with INDEX and MATCH, you can use the EXACT function to compare the text strings in the students’ name range to YUKI, the value in the cell G5, including the case of each character. Then we can use the INDEX and MATCH functions together to find the value we want.

Generic syntax

=INDEX(return_range,MATCH(TRUE,EXACT(lookup_value,lookup_range),0))

√ Note: This is an array formula that requires you to enter with Ctrl + Shift + Enter.

To know the grade of YUKI, please copy or enter the formula below in the cell G6, and press Ctrl + Shift + Enter to get the result:

=INDEX(D5:D14,MATCH(TRUE,EXACT("YUKI",B5:B14),0))

Or, use a cell reference to make the formula dynamic:

=INDEX(D5:D14,MATCH(TRUE,EXACT(G5,B5:B14),0))

Explanation of the formula

=INDEX(D5:D14,MATCH(TRUE,EXACT("YUKI",B5:B14),0))

• EXACT(G5,B5:B14): The EXACT function compares the text strings in the students’ name range B5:B14 against "YUKI", the value in the cell G5, returns a TRUE if a value in the cells through B5 to B14 is exactly the same as YUKI, a FALSE otherwise. So, we will get an array of TRUEs and FALSEs like this:
  {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}
• MATCH(TRUE,EXACT(G5,B5:B14),0) = MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},0): The match_type 0 asks the MATCH function to find the exact match. The function will then return the position of its exact lookup_value “TURE” in the array, which is 10, since the only TRUE is at the 10th position in the array.
• INDEX(D5:D14,MATCH(TRUE,EXACT(G5,B5:B14),0)) = INDEX(D5:D14,10): The INDEX function returns the 10th value in the grade range D5:D14, which is A.

Perform a case-sensitive lookup with VLOOKUP

To know the country that JIMMY comes from with the VLOOKUP function, you should take advantage of the CHOOSE and EXACT functions to perform a case-sensitive lookup. The EXACT function will conduct a case-sensitive comparison between JIMMY and each text string in the students’ name range. Then we can use CHOOSE to combine the EXACT’s result and the values in the country range. Now it’s time for VLOOKUP to show its ability.

Generic syntax

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(lookup_value,lookup_range),return_range),2,FALSE)

√ Note: This is an array formula that requires you to enter with Ctrl + Shift + Enter.

To know the country JIMMY comes from, please copy or enter the formula below in the cell G9, and press Ctrl + Shift + Enter to get the result:

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT("JIMMY",B5:B14),C5:C14),2,FALSE)

Or, use a cell reference to make the formula dynamic:

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14),2,FALSE)

Explanation of the formula

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14),2,FALSE)

• EXACT(G8,B5:B14): The EXACT function compares the text strings in the students’ name range B5:B14 against the value in the cell G8, JIMMY, returns a TRUE if a value in the students’ name range is exactly the same as JIMMY, a FALSE otherwise. So, we will get an array of TRUEs and FALSEs like this:
  {FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
• CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14) = CHOOSE({1,2},{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},C5:C14): The index_num argument of CHOOSE {1,2} combines the EXACT array and the values from C5:C14 into a two-dimensional array like this:
  {FALSE,"China";FALSE,"England";FALSE,"America";FALSE,"India";TRUE,"America";FALSE,"India";FALSE,"America";FALSE,"China";FALSE,"England";FALSE,"China"}
• VLOOKUP(TRUE,CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14),2,FALSE) = VLOOKUP(TRUE,{FALSE,"China";FALSE,"England";FALSE,"America";FALSE,"India";TRUE,"America";FALSE,"India";FALSE,"America";FALSE,"China";FALSE,"England";FALSE,"China"},2,FALSE): The range_lookup FALSE askes the VLOOKUP function to search for the exact value “TRUE” in the 1st column of the two-dimensional array, and returns its exact match in the 2nd column, which is America.

Related functions

Related Formulas