Have you ever felt lost in a huge Excel spreadsheet especially when you need to look up a value along the rows or columns? Do you often scan through large Excel datasets to pinpoint interesting data and create a list of those manually, and put a huge strain on your eyes in the process? Then, you must how to use INDEX MATCH in Excel to perform advanced lookups in Excel.

When manipulating, analyzing, and visualizing datasets to extract actionable insights, you often need to look up queried or interesting data from a large dataset. I know you could go through each row, column by column to find the individual data, make a note of that, and repeat the process. But, that’s really not the process you should follow if you’re using Microsoft Excel in the first place.

In such a situation, you can combine INDEX and MATCH functions to locate data by providing Excel a reference or hint to which the queried data is related.

Whether you’re a seasoned Excel user or a beginner just starting out, this article will provide a step-by-step walkthrough to help you harness the full potential of INDEX MATCH. By the end of this guide, you’ll be able to perform complex lookups with ease, making your data analysis more efficient and insightful. Let’s begin!

What Is INDEX and MATCH in Excel?

Before you can learn the technical use cases of INDEX and MATCH functions in a single Excel formula, you must learn the basics of these formulas and how to write these functions.

INDEX

The INDEX formula in Excel allows you to retrieve the value of a cell within a specified range based on its row and column numbers. It’s a powerful dataset-crunching function commonly used for data lookup and array manipulation tasks. This formula is valuable for dynamic data analysis, allowing you to extract information based on specific criteria.

The INDEX formula syntax in Excel is:

=INDEX(array, row_num, [column_num])

• Array: This is the range or array of data from which you want to retrieve a value. It can be a single row, a single column, or multiple rows and columns. The array should exclude the column headers of the dataset.
• Row_Num: This is the row number within the array from which you want to retrieve the value. It can be a number, array, or reference to a single cell containing a number. The row number count works according to the highlighted array. If you’ve selected A2:A10, then cell A2 is row 1 and cell A10 is row 9.
• [Column_Num]: This is the column number within the array from which you want to retrieve the value. If omitted, the column number defaults to the same as the row number. It’s optional if the array is one-dimensional. It’s only required when you include a lookup range consisting of multiple columns.

MATCH

You can use the MATCH formula in Excel to locate the position of a specified value within a range or array. It’s just the opposite of the INDEX function.

The MATCH formula syntax in Excel is:

=MATCH(lookup_value, lookup_array, [match_type])

• Lookup_Value: This is the value you want to search for within the lookup_array.
• Lookup_Array: This is the range of cells or array where Excel should search for the Lookup_Value.
• [Match_Type]: This parameter specifies the type of match. This is an optional argument. Here are the notations and coding for Match_Type:
  • 1 or omitted: Finds the largest value less than or equal to the Lookup_Value and it’s the default setting.
  • 0: Finds an exact match and is hence suitable for text-based value searches.
  • -1: Finds the smallest value greater than or equal to the Lookup_Value.

The INDEX function requires a row number to locate the required value of a cell and the MATCH function can generate that row number.

Hence, by using MATCH as the row argument for INDEX you can easily locate individual values based on a criteria from massive dataset.

Advantages of INDEX MATCH Over VLOOKUP

• INDEX MATCH can look up values in both rows and columns, while VLOOKUP is limited to searching in columns.
• VLOOKUP always requires the looked-up value to be in the leftmost column of the look-up table.
• INDEX MATCH allows for dynamic column selection, meaning you can easily change the column reference without modifying the formula.
• With INDEX MATCH, if you rearrange columns in your dataset, the formula remains unaffected, whereas VLOOKUP requires adjusting column references.
• INDEX MATCH handles errors more gracefully, returning #N/A only when no match is found, whereas VLOOKUP might return incorrect results if the column index is changed.
• In large datasets, INDEX MATCH tends to perform better than VLOOKUP due to its computational efficiency.
• INDEX MATCH can handle multiple criteria lookups more efficiently than VLOOKUP combined with helper columns.
• INDEX MATCH can handle non-contiguous data ranges, which VLOOKUP cannot.

Simple INDEX and MATCH

Let’s try to understand the use of the INDEX MATCH combination formula in a simple dataset as shown above. In the above dataset, I’m going to locate the Candidates based on their Country using INDEX and MATCH.

For example, I need to find the candidate’s name from the US.

So, first I’ll enter the following MATCH formula in the destination cell to locate the row number where the target data is available in the look-up table.

=MATCH(F1,B2:B7,0)

After hitting Enter, I get the row number in the destination cell.

Now, I’m going to expand the same formula with INDEX, making the MATCH formula the row argument of the INDEX column.

Here’s the final combination of the formula:

=INDEX(A2:A7,MATCH(F1,B2:B7,0))

I’ve entered the above formula in the destination cell F2 and pressed Enter to get the queried value which is Margaret M.

Combine Table Data Using INDEX and MATCH

You can also creatively use this combination formula to import table data from a second table to the first table to make your dataset more comprehensive and organized.

For example, in the dataset shown above, you want to import rebate data to TABLE 1 from TABLE 2, so you can get rid of the second table.

So, I’ll show you how can you use INDEX and MATCH in Excel to assign discount percentages below the Rebate column in TABLE 1 by referring to categorical rebates in TABLE 2.

The first step is to find the row number by matching categories using the following MATCH formula in D3, the first cell below the Rebate column:

=MATCH([@Category],$G$3:$G$7,0)

Now that you’ve got the row number where the categorical discount value is located, expand the formula to make the MATCH formula the row argument of the INDEX formula. Then, you only need to highlight the Disc % column values so INDEX can populate the corresponding cell value in D3.

=INDEX($F$3:$F$7,MATCH([@Category],$G$3:$G$7,0))

Upon hitting Enter in D3, you get 0.1, the value of the first row located by the MATCH function.

Select D3 and click the % sign in the Number commands block of the Home tab in Excel to convert decimals to percentage values.

Now, drag the fill handle from D3 down until D12 to populate discount percentages for the rest of the hardware products.

Now, you can delete TABLE 2 to declutter your dataset.

Extract Data to a Table Using INDEX and MATCH

Often, you get unstructured and stacked datasets from which you can extract the important values to the main table for further storage or analysis of data. You can do so using the combination formula of INDEX and MATCH.

In the above dataset, the vendor emails have been recorded in a stacked manner, which is a challenge to implement any automation to extract vendor emails by hardware category and put that below the Vendor Email column.

However, if you introduce a little modification to the INDEX MATCH formula, you can populate the vendor emails relevant to the category of the products in the table.

Let’s find the row number for the relevant category in column E and put that in the cell C2 of the Vendor Email column. For this, you can use this MATCH formula. Hit Enter to get the row number.

=MATCH(B2,$E$2:$E$11,0)

Now, you can expand the above formula with INDEX to populate the value of the cell in the retrieved row number. In the formula, you must add 1 so that Excel can get the vendor email just below the row number fetched. Here’s the complete formula syntax:

=INDEX($E$2:$E$11,MATCH(B2,$E$2:$E$11,0)+1)

Now, simply expand the same INDEX MATCH formula down the Vendor Email column using the fill handle to get vendor emails for the rest of the hardware products.

INDEX and MATCH With Multiple MATCH Arrays

Sometimes, you might want to use more than one look-up value in MATCH to fetch row numbers which in turn you can use in INDEX to fetch the intended value.

Suppose, you’ve got a country and category-wise product discount worksheet as shown in the above screenshot.

Now, you want to refer to the Disc % column in TABLE 2 and populate the appropriate discount percentages in the Rebate column of TABLE 1.

First, you need to use the following MATCH formula which contains two look-up arrays in the cell E3:

=MATCH(1,($H$3:$H$12=B3)*($I$3:$I$12=C3),0)

The above formula populates the relevant row numbers from TABLE 2 for the hardware products in TABLE 1.

Now, expand the above formula with the INDEX formula to fetch the relevant discount amount in E3. The combination formula shall be as given below:

=INDEX($G$3:$G$12,MATCH(1,($H$3:$H$12=B3)*($I$3:$I$12=C3),0))

You shall get the decimal value 0.1 in E2. Which can be easily converted to percentage value from the Home > Number > % button.

Now, use the fill handle to apply the same formula and number formatting to the rest of the Rebate column in TABLE 1.

If you’re performing this method in earlier Excel desktop versions than Excel for Microsoft 365, you must press Ctrl + Shift + Enter to calculate the result instead of just the Enter key.

Row and Column-Wise Lookup Using INDEX and MATCH

So far, you’ve learned the techniques to use INDEX and MATCH together to fetch cell values by a queried row. However, you can also create lookup models where you can produce a cell value from the look-up table by referencing a row number and column number.

In this method, you’ll be using two MATCH functions to get the row number and column number arguments for INDEX. In turn, you get the cell value you’ve been looking for.

In the above dataset, I want to assign discount percentages for the hardware products below the Rebate column in TABLE 1. My reference table to assign discounts is TABLE 2. TABLE 2 has columns for product category and rebate percentages by country.

Let’s create the first MATCH for row number and the second MATCH for column number by using the following formula in E3:

=MATCH([@Category],$G$3:$G$7,0),MATCH([@Country],$H$2:$I$2,0)

I can’t press Enter just yet because doing so shall generate a formula error in Excel.

I must also add the INDEX formula to the above partial formula. The final INDEX MATCH formula becomes as given below:

=INDEX($H$3:$I$7,MATCH([@Category],$G$3:$G$7,0),MATCH([@Country],$H$2:$I$2,0))

Now, I can simply use the fill handle to populate the discount values for the rest of the cells in the Rebate column.

Conclusions

So far, you’ve discovered different ways to use the INDEX and MATCH functions in Excel to look up values easily with real-world datasets.

If you’ve tried all or some of the above methods, comment below which one you liked the most. Also, comment if you know a better trick and tip involving how to use INDEX MATCH in Excel.