Exceljet

Quick, clean, and to the point

Case-sensitive INDEX and MATCH

Excel formula: Case-sensitive INDEX and MATCH
Generic formula 
=INDEX(data,MATCH(TRUE,EXACT(value,range),0),col_num)
Summary 

To perform a case-sensitive lookup with INDEX and MATCH, you can use the EXACT function. In the example shown, the formula in cell G6 is:

=INDEX(data,MATCH(TRUE,EXACT(F6,data[First]),0),3)

where data is an Excel Table in the range B5:B104. The result is a case-sensitive match for "JANET", which appears as the second "Janet" in the list of names. The final result returned by INDEX is "Fulfillment". Cell G5 contains a standard formula that is not case-sensitive.

Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365.

Explanation 

In this example, the goal is to perform a case-sensitive lookup on the first name in column B, based on a lookup value in cell F6. By default, Excel is not case-sensitive and this applies to standard lookup formulas like VLOOKUP, XLOOKUP, and INDEX and MATCH. These formulas will simply return the first match, ignoring case. For example, the formula in cell G5 is a standard INDEX and MATCH formula that is not case-sensitive:

=INDEX(data,MATCH(F5,data[First],0),3) // returns "Sales"

Since MATCH is not case sensitive, it returns the first match for "Janet" in row 2 of the table, even though the lookup value is "JANET" in uppercase.

We need a way to get Excel to compare case. The EXACT function is perfect for this task, but the way we use it is a little unusual. Instead of comparing one text value to another, we compare one text value to many values. In a nutshell, we use the EXACT function to generate an array of TRUE and FALSE values, then use the MATCH function to locate the first TRUE.

Background reading

This article assumes you are familiar with Excel Tables and INDEX and MATCH. If not, see:

EXACT function

Working from the inside out, we first use the EXACT function to compare the lookup value in F6 with every value in the "First" column in the table:

EXACT(F6,data[First])

Because we give EXACT an array of values as a second argument, we will get back an array of TRUE and FALSE results. The first 10 results in this array look like this:

{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;...

Note: the actual array will contain 100 values, since there are 100 rows in the table.

This array is created by checking the value in F6 against every cell in the "First" column of the table. A TRUE value indicates an exact, case-sensitive match. A FALSE value means no exact match. Based on the results above, we have an exact match at row 6 of the table.

MATCH function

Now we need to get the position (row number) of the TRUE value in this array. With INDEX and MATCH formulas, this is done with the MATCH function. The twist in this case is that we set up MATCH to look for TRUE instead of the original lookup value in F6:

MATCH(TRUE,EXACT(F6,data[First]),0) // returns 6

Inside the MATCH function, lookup_value is TRUE, lookup_array comes from EXACT, and the match_type argument is set to 0 for exact match. It may seem strange to ask MATCH to look for TRUE, but remember that the EXACT function is creating an array of TRUE and FALSE values. When EXACT delivers this array to MATCH, the original data is no longer available. The MATCH function returns 6.

Note: MATCH will always return the first match if there are duplicate lookup values.

INDEX and MATCH

Now that we have a row number, we just need to use the INDEX function to retrieve the value at the right row and column intersection. The MATCH function returns the number 6 for row_num, as explained above. The column_num argument is hardcoded as 3, since array is given as data, which includes all three columns. The final formula is:

=INDEX(data,MATCH(TRUE,EXACT(F6,data[First]),0),3)

With a row number of 6 and a column number of 3, INDEX returns a final result of "Fulfillment".

With XLOOKUP

With the XLOOKUP function, we can build a more compact formula with the same result:

=XLOOKUP(TRUE,EXACT(F6,data[First]),data[Department])

This formula works exactly the same as the INDEX and MATCH option explained above. The EXACT function is used to compare all values in the the "First" column of the table with the value in cell F6. The result is returned directly to XLOOKUP as the lookup_array. The lookup_value is set to TRUE, and return_array is the "Department" column. For a more detailed explanation with XLOOKUP, see this example.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.