**INDEX and MATCH** are two functions that are most often used together. They are far superior that the **VLOOKUP** function that is maybe even more used.

In the example below, we will show how to use **INDEX** and **MATCH** in multiple sheets.

Tutorial Content

## Quick recap

The Excel **INDEX** function is used to return the value of a cell at a given position in a range or array.

Syntax of this function is as follows:

1 |
=INDEX(array, row_num, [col_num], [area_num]) |

Arguments are:

**array – **It can be a range of cells, tables, text, or anything where our values are found.

**row_num** – This represents a row position in the defined array.

**col_num** – [optional] This represents a column option in the defined array.

**area_num** – [optional] Here we input the range in reference that we should use.

The** MATCH function **is used to search a specified item in a range and then to return the relative position of that item in the range.

Its syntax is:

1 |
=MATCH(lookup_value, lookup_array, [match_type]) |

Arguments are as follows:

**lookup_value:** This is a required field. Here we input the value that we want to find in lookup_array.

**lookup_array: **Also required. We declare the range where our value is located.

**match_type:** This is an optional field that can have three values: -1, 0, or 1. This argument defines how Excel matches **lookup_value** with values in **lookup_array**. **Value 0** represents the exact value of the **lookup_value**, and we will use this one.

## Index and Match from Another Sheet

For our example, we will use the list of all Marvel movies, along with their respectful revenue rating (in comparison with the other Marvel movies on a list), release year, release date in the US, and box office gross revenue. We will put this table into a sheet called **“Marvel movies”.**

To create the **Index** and **Match** table, we will first create another sheet and call it simply** „Index & Match“**.

In that sheet, values in **column A **will be as follows (these are the values that can be found in the first row of our original table):

**Revenue rating** will be our lookup value for **row_num** (this value will be in cell B1), while other values in column A will be our lookup values for **column_num** of our **INDEX** function.

Having that in mind, we will randomly put number 3 in **cell** **B1**.

In cell **B2** we will input the formula as follows:

1 |
=INDEX('Marvel Movies'!A:E,MATCH($B$1,'Marvel Movies'!B:B,0),MATCH($A2,'Marvel Movies'!$1:$1,0)) |

We will explain the formula in detail below:

First, we start by entering our **INDEX** formula.

The first argument that we have to enter in the formula is an **array**. Since we will be needing data from the whole table, our array will be every cell in a range from **column A to column E**.

So, our array is **‘**Marvel Movies’!A:E (In the first part of the string we are referencing the sheet with the data. That is why we have apostrophes surrounding the name of our sheet. We have “!” as well since that is also an obligatory part when referencing another sheet. Finally, we added the columns).

Next, we need to define **row_num**, i.e. we have to find a position of our desired cell in rows. That is where the **MATCH function** comes into the picture. We enter it and that we type in our desired **lookup_value** (**cell B1**). We lock this cell, as we want to use this cell’s reference for searching different values.

Then we define **lookup_array** (where this value could be located in our table. We know it is **column B**, so we reference the whole column in the **“Marvel Movies”** sheet, and **match_type** (0 for searching exact value).

Finally, we have to define the **column_row**. The reference cell is **cell A2**. We will only lock the column for this cell, so it will always be a **column A**, but the rows can be changed. Our **lookup_array** is found in the **“Marvel Movies”** sheet again, and the value is located in the first row, hence the syntax: ‘Marvel Movies’!$1:$1.

We also need to lock these cells as well, as all of our values located in **column A** in this sheet are located in the first row in sheet **“Marvel Movies”**. Our **match_type** is again 0.

The result of our formula is as follows:

With this formula and defined cells in this way, as well as locked cells and the order of the columns in the first column, we can simply just copy and paste our formula to the cells in the **B column** (**from B2 to B5**).

When we do that, we will have all of our data populated:

The formula in cell **B3** is as follows:

1 |
=INDEX('Marvel Movies'!A:E,MATCH($B$1,'Marvel Movies'!B:B,0),MATCH($A3,'Marvel Movies'!$1:$1,0)) |

We can see that, in comparison with the first formula that we explained, the only difference is that we now have **cell A3** instead of **cell A2** as the **lookup_value** in the second **MATCH formula**, which defines the **column_num** for our **INDEX formula**.

We could have used only the data from the table. We will do that for release year. The formula looks like this:

1 |
=INDEX(Table1[#All],MATCH($B$1,Table1[[#All],[Revenue rating]],0),MATCH($A5,Table1[#Headers],0)) |

As seen, for the **INDEX array Table1[#All]** is used. Table1 is the name of our table, and **„All“** stands for all the data.

**Lookup_array** for the first **MATCH function** is **Table1[[#All],[Revenue rating]]**, i.e the column where revenue rating is located.

**Lookup_array** for the second **MATCH function** is the **Headers** from our table, which is the first row.

If we would add a row or a column to our table, this row or column would become an integral part of the table and we could apply an existing formula to find the desired value.