INDEX and MATCH Function in Excel

The INDEX function retrieves a value from a specified location within a range, using row and column offsets. It is a tool for pinpointing data in a table.

INDEX Function Syntax in Excel:

=INDEX(reference, [row], [column])

Parameters:

• array: The range containing the data (e.g., A1:C10).
• row_num: The row number of the value to retrieve.
• column_num (optional): The column number of the value to retrieve.

How to Use an INDEX Function in Excel

Follow the below steps to learn how to use INDEX Function in Excel:

Step 1: Select the Data Range

Identify the range where our data is stored, such as A1:C10.

Step 2: Specify the Row Number

Indicate the row containing the desired data point.

Step 3: Specify the Column Number

For multi-column ranges, provide the column number to locate the exact cell.

The following table will be used as a reference table for all the examples of the INDEX function. First Cell is at B3 (“FOOD”) and the Last Diagonal Cell is at F10 (“180”).

Example 1: No Rows and Columns are mentioned.

If neither the row nor the column is specified, the INDEX function will return the entire data range.

Step 1: Input Command

Use Command "=INDEX(B3:C10)"        

Step 2: Preview Result          

Preview the below result

Example 2: Only Rows are Mentioned

If only the row number is provided, the INDEX function will return all values in that row within the range.

Step 1: Input Command

=INDEX(B3:C10,2)                  

Step 2: Preview Result

Returns all values from the second row of the range (e.g., B4:C4).

Example 3: Both Row and Column Specified

When both the row and column numbers are specified, the INDEX function will return the value from the specific cell.

Step 1: Input Command

=INDEX(B3:D10,4,2)                    

Step 2: Preview Result

Returns the value from the fourth row and second column of the range (e.g., C6).

Example 4: Only Column Specified

If only the column number is provided, the INDEX function will return all values from that column within the range.

Step 1: Input Command

=INDEX(B3 : D10 , , 2)              

Step 2: Preview Results

Returns all values from the second column of the range (e.g., C3:C10).

MATCH Function in Excel

The MATCH function in Excel is used to locate the position of a specific value within a row, column, or array. Unlike VLOOKUP or HLOOKUP, the MATCH function does not return the actual data but instead provides the relative position of the value. It is case-insensitive and works seamlessly in both horizontal and vertical ranges.

MATCH Function Syntax

=MATCH(search_key, range, [search_type])

Parameters:

• lookup_value: The value to search for (e.g., “South Indian” or 42).
• lookup_array: A single row or column to search (e.g., A1:A10).
• match_type (optional):
• +1: Largest value less than or equal to lookup_value (ascending order).
• 0: Exact match (unsorted data, most common).
• -1: Smallest value greater than or equal to lookup_value (descending order).

How MATCH Works with INDEX

The MATCH function is often paired with the INDEX function to dynamically determine the row or column number, enabling precise data retrieval. For instance, if we want to find the position of an item and use that position in the INDEX function to retrieve corresponding data, the MATCH function provides a seamless solution.

How to Use MATCH Function in Excel

Follow the below steps to learn how to use MATCH Function in Excel:

Step 1: Choose the Lookup Value

• Identify the value to locate (e.g., a number or text).

Step 2: Select the Lookup Range

Define the one-dimensional range (row or column) where Excel should search for the lookup value.

Step 3: Specify the Match Type

• Use 0 for an exact match.
• Use 1 for an approximate match in ascending order.
• Use -1 for an approximate match in descending order.

MATCH Function Example

Reference Table: The following table will be used as a reference table for all the examples of the MATCH function. First Cell is at B3 (“FOOD”) and the Last Diagonal Cell is At F10 (“180”)

Example 1: Exact Match (match_type = 0)

Find the row number of the food type "South Indian".

Step 1: Input Command

=MATCH(“South Indian”,C3:C10,0)                                 

Step 2: Preview Results

The function returns 1, as "South Indian" first appears in the first row of the lookup range.

Example 2: Approximate Match in Ascending Order (match_type = 1)

Find the position of the largest value less than or equal to 70 in the Cost column.

Step 1: Input Command

=MATCH(“South Indian”,C3:C10)                           

• Sort the Cost column in ascending order.
• Input the formula and press Enter.

Step 2: Preview Results

The function returns 2, as the largest value less than or equal to 70 is 50 (located in the second row of the range).

Example 3: Approximate Match in Descending Order (match_type = -1)

Find the position of the smallest value greater than or equal to 80 in the Cost column.

Step 1: Input Command

=MATCH(80, D3:D6, -1)

• Sort the Cost column in descending order.
• Input the formula and press Enter.

Step 2: Preview Results

The function returns 2, as the smallest value greater than or equal to 80 is 80 (located in the second row of the range).

How to use the INDEX MATCH Excel Function Combination

The combination of INDEX and MATCH in Excel is a dynamic and efficient way to perform data lookups. Unlike using static row and column numbers in the INDEX function, we can use the MATCH function to dynamically determine the positions of rows and columns based on specific criteria. This method is not only flexible but also eliminates many limitations of VLOOKUP.

Syntax:

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), MATCH(lookup_value, lookup_array, [match_type]))

Example Reference Table

The following reference table will be used. First Cell is at B3 (“FOOD”) and the Last Diagonal Cell is At F10 (“180”)

Example: Let's say the task is to find the cost of Masala Dosa. It is known that column 3 represents the cost of items, but the row position of Masala Dosa is not known. The problem can be divided into two steps:

Step 1: Find the Row Position

Find the position of Masala Dosa by using the formula:

=MATCH("Masala Dosa",B3:B10,0)

Here B3:B10 represents Column “Food” and 0 means Exact Match. It will return the row number of Masala Dosa.

Step 2: Use INDEX Function

Find the cost of Masala Dosa. Use the INDEX Function to find the cost of Masala Dosa. By substituting the above MATCH function query inside the INDEX function at the place where the exact position of Masala Dosa is required, and the column number of cost is 3 which is already known. 

=INDEX(B3:F10, MATCH("Masala Dosa", B3:B10 , 0) ,3)

Two-Way Lookup with INDEX and MATCH

In the above example, the column position for the cost was hardcoded. If we want to make the column lookup dynamic as well, use a MATCH function for the column position.

Example: Find the cost of "Masala Dosa" dynamically:

=INDEX(B3:F6, MATCH("Masala Dosa", B3:B6, 0), MATCH("Cost", B3:F3, 0))

Here B3:F3 represents Header Column.   

Result: 60

Using INDEX MATCH For Advanced Lookups

One of the key advantages of INDEX and MATCH over the VLOOKUP function is the ability to perform a “left lookup”. It means it is possible to extract the row position of an item from using any attribute at right and the value of another attribute in left can be extracted. 

For Example, Let's say buy food whose cost should be 140 Rs. Indirectly we are saying buy “Biryani”. In this example, the cost Rs 140/- is known, there is a need to extract the “Food”. Since the Cost column is placed to the right of the Food column. If VLOOKUP is applied it will not be able to search the left side of the Cost column. That is why using VLOOKUP it is not possible to get Food Name. 

1. Left Lookup with INDEX and MATCH

The INDEX MATCH combination can perform a left lookup, which is not possible with VLOOKUP.

Example: Find the food item where the cost is 140.

Step 1: Extract Row Position

First extract row position of Cost 140 Rs using the formula:

=MATCH(140, D3:D10,0)

Here D3: D10 represents the Cost column where the search for the Cost 140 Rs row number is being done. 

Step 2: Use INDEX Function

After getting the row number, the next step is to use the INDEX Function to extract Food Name using the formula:

=INDEX(B3:B10, MATCH(140, D3:D10,0))

Here B3:B10 represents Food Column and 140 is the Cost of the food item.

Result: Biryani

2. Case-Sensitive Lookup

The MATCH function is not case-sensitive by default. To perform a case-sensitive lookup, use the EXACT function in combination with INDEX.

By itself, the MATCH function is not case-sensitive. This means if there is a Food Name “DHOKLA” and the MATCH function is used with the following search word:

1. “Dhokla”
2. “dhokla”
3. “DhOkLA”

All will return the row position of DHOKLA. However, the EXACT function can be used with INDEX and MATCH to perform a lookup that respects upper and lower case.

Exact Function: The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.

Examples:

1. EXACT("DHOKLA","DHOKLA"): This will return True.
2. EXACT("DHOKLA","Dhokla"): This will return False.
3. EXACT("DHOKLA","dhokla"): This will return False.
4. EXACT("DHOKLA","DhOkLA"): This will return False.

Example: Let say the task is to search for the Type Of Food “Dhokla” but in Case-Sensitive Way. This can be done using the formula-

=INDEX(C3:C10, MATCH(TRUE , EXACT("Dhokla", B3:B10) ,0))

Here the EXACT function will return True if the value in Column B3:B10 matches with “Dhokla” with the same case, else it will return False. Now MATCH function will apply in Column B3:B10 and search for a row with the Exact value TRUE. After that INDEX Function will retrieve the value of Column C3:C10 (Food Type Column) at the row returned by the MATCH function.        

3. Multiple Criteria Lookup

One of the trickiest problems in Excel is a lookup based on multiple criteria. In other words, a lookup that matches on more than one column at the same time. In the example below, the INDEX and MATCH function and boolean logic are used to match on 3 columns-

• Food.
• Cost.
• Quantity.

To extract total cost.

Example: Let's say the task is to calculate the total cost of Pasta where 

• Food: Pasta.
• Cost: 60.
• Quantity: 1.

So in this example, there are three criteria to perform a Match. Below are the steps for the search based on multiple criteria-

Step 1: Match the Food Column

First match Food Column (B3:B10) with Pasta using the formula:

"PASTA" = B3:B10

This will convert B3:B10 (Food Column) values as Boolean. That Is True where Food is Pasta else False. 

Step 2: Match Cost

After that, match Cost criteria in the following manner:

60 = D3:D10

This will replace D3:D10 (Cost Column) values as Boolean. That is True where Cost=60 else False. 

Step 3: Match Third Criteria

Next step is to match the third criteria that are Quantity = 1 in the following manner:

1 = E3:E10

This will replace E3:E10 Column (Quantity Column) as True where Quantity = 1 else it will be False.

Step 4: Multiply the Results

Multiply the result of the first, second, and third criteria. This will be the intersection of all conditions and convert Boolean True / False as 1/0.

Step 5: Use MATCH Function

Now the result will be a Column with 0 And 1. Here use the MATCH Function to find the row number of columns that contain 1. Because if a column is having the value 1, then it means it satisfies all three criteria. 

Step 6: Use INDEX Function

After getting the row number use the INDEX function to get the total cost of that row. 

=INDEX(F3:F10, MATCH(1, ("Pasta"=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))

 Here F3:F10 represents the Total Cost Column.                      

4. Using INDEX and MATCH Across Different Sheets

To retrieve data from a different sheet, simply reference the sheet name.

To use the INDEX and MATCH functions across different sheets in Excel, we simply need to reference the sheet name in the formula. This method allows we to search for data in one sheet and return it to another, making it a powerful tool for multi-sheet data analysis.

Basic Syntax for INDEX and MATCH Across Sheets

=INDEX(SheetName!Range, MATCH(LookupValue, SheetName!LookupRange, MatchType))

• Sheet1 (where we want to display the result).
• Sheet2 (where the data is stored).

Step 1: Set up wer Data on Sheet2

In Sheet2, We have a table with names and scores:

Step 2: Write the Formula on Sheet1

In Sheet1, let’s say we want to look up the score for "Jordan" from Sheet2.

Go to Sheet1 where we want the result to display.

Click in the cell where we want the result (e.g., cell A1).

Type the formula as follows:

=INDEX(Sheet2!B2:B6, MATCH("Jordan", Sheet2!A2:A6, 0))

• Sheet2!B2:B6 is the range where the scores are stored on Sheet2.
• MATCH("Jordan", Sheet2!A2:A6, 0) searches for "Jordan" in the A column on Sheet2 and returns the row position.
• INDEX then uses this row position to find Jordan’s score in the B column on Sheet2.

Step 3: Press Enter to see the result

In this example, we should get 88, which is Jordan’s score.