How to Use INDEX & MATCH in Microsoft Excel

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

Using INDEX to retrieve cell value
Using INDEX to retrieve cell value

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

Location row number using MATCH
Location row number using 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.

Sample dataset for simple INDEX MATCH
Sample dataset for simple INDEX MATCH

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

Writing the MATCH part of INDEX MATCH
Writing the MATCH part of INDEX MATCH

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.

Using INDEX MATCH to locate queried value
Using INDEX MATCH to locate queried value

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.

Sample for table data import
Sample for table data import

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.

Populating relevant row number using MATCH
Populating relevant row number using MATCH

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)
Use INDEX MATCH to get discount
Use INDEX MATCH to get discount

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.

Convert decimals to percent
Convert decimals to percent

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

Using fill handle to copy formula
Using the fill handle to copy formula

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.

Vendor email dataset
Vendor email dataset

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.

Get row number from vendor email
Get row number from vendor email

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)
Fetch email from another table
Fetch email from another table

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)
Using fill handle to fetch emails
Using fill handle to fetch emails

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.

The example dataset for double MATCH
The example dataset for double MATCH

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.

Double Match lookup and array
Double Match lookup and array

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.

Using INDEX MATCH for double lookup
Using INDEX MATCH for double lookup

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))
Decimal to percent in Home
Decimal to percent in Home

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

Using INDEX MATCH with multiple lookup arrays
Using INDEX MATCH with multiple lookup arrays

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.

Sample dataset for double MATCH

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.

Double MATCH by row and column
Double MATCH by row and column

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.

INDEX with double MATCH
INDEX with double MATCH

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))
Using advanced INDEX MATCH
Using advanced INDEX MATCH

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.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

2 Comments

  1. Adiv Abramson

    Great solutions to very common and challenging issues with merging disparate sets of data. I feel that I have learned valuable data manipulation techniques from this presentation.

    As a relative newbie to the wonderful world of Power Query, I am constantly challenging myself to use it where possible to achieve the same outcomes as built-in Excel functions. I prefer Power Query because formulas can slow down workbook performance and can appear rather daunting. I convert the ranges that need to be somehow merged into tables as a first step.

    For the exercise labeled “Extract Data to a Table Using INDEX and MATCH”, I was able to come up with a Power Query solution that requires no formulas at all. When I add or edit records from either table, I just click Data > Refresh All and the merged table result is updated to reflect the changes. I never need to copy down or adjust formulas. For these reasons, I prefer using Power Query to tackle the types of challenges covered in this blog post.

    Thank you kindly.

    Reply
  2. Dani W

    This is really helpful!

    But I don’t understand this section: “INDEX and MATCH With Multiple MATCH Arrays”, Match formula. What syntax is it following? What does the first “1” mean? What does the “*” mean? How does it reference both values?

    Thanks!

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃