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(array, row_num, [column_num])

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.

Steps 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 the 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 row and column are not specified properly, the INDEX function may return an error or require array formula usage. Typically, at least the row number must be provided.

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 finds the position of a value within a row, column, or array. Unlike VLOOKUP/HLOOKUP, it returns the position, not the data, and works case-insensitively 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).

Using MATCH with INDEX

The MATCH function is commonly used with INDEX to find a row or column number, allowing precise data retrieval. MATCH identifies the position of a value, which INDEX then uses to return the corresponding data.

Steps 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(70, D3:D10, 1)                      

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).

Steps 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

INDEX and MATCH can perform a “left lookup,” allowing retrieval of a value from a column to the left of the search column something VLOOKUP cannot do.

Example: To find the food costing ₹140, MATCH locates the cost, and INDEX retrieves the corresponding food name from the left column. 

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's 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))

The EXACT function checks if values in B3:B10 match “Dhokla” with the same case, returning TRUE or FALSE. MATCH then finds the row with TRUE, and INDEX retrieves the corresponding value from C3:C10 (Food Type).        

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 users 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 your Data in 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.