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.

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.

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!