How to Use INDEX MATCH Across Multiple Sheets in Excel or Google Sheets

If VLOOKUP or XLOOKUP just aren't working for what you need to find the data you want, INDEX MATCH is the perfect option for you.

INDEX MATCH simplifies finding the data you need, especially if you have multiple sheets that you need to pull the data from in Excel or Google Sheets.

This is also a great way to make reading your data more fun and interactive for the user.

use INDEX MATCH on multiple sheets Excel or Google Sheets

Step 1: Organize the data

INDEX MATCH will work whether your data is listed in one tab or on multiple tabs in your spreadsheet. For this example, we will have all of our data split into different tabs.

Decide how you would like your data to appear. If you have a lot of information, keeping everything on one tab might become overwhelming. However, splitting the data to separate tabs may not be an option if

In this example, we will be working with these tabs:

To start building the table where the INDEX MATCH data will be displayed, let's start with the 4 rows we want to see data for. This table will be built on the Main Data tab:

President
Vice President
Years of Service
Congresses

In this case, we want to display the data based on the name of the president, therefore, we need to create a dropdown that lists all of the president's names. Once a president's name is selected from the dropdown, the following three rows of data will be populated accordingly using the INDEX MATCH formula.

Step 1: Set Up the Dropdown Field (Data Validation)

Setting up a dropdown field in Excel or Google Sheets is very easy and makes your spreadsheet much more interactive.

For the purposes of this example, and to keep everything as clean and organized as possible, we will pull all the president names from the Presidents tab into the dropdown field using Data Validation.

How to set up a dropdown field in Excel

Select the cell in which you would like to add to the dropdown field. In this example, you'll want to place it one cell to the right of President in the Main Data tab.

Next, click on the Data tab at the top. In Data Tools, click on the Data Validation icon, which looks like this:

After clicking the icon, you'll see a popup modal where you'll be able to select the data source. Under Validation criteria, the Allow field should say List and once this is selected, the Source section will become available. In the source selection, you are going to click on the Presidents tab and select all of the cells with President names.

How to set up a dropdown field in Google Sheets

The process for setting up a dropdown field in Google Sheets is even easier.

Select the cell where you would like the dropdown list to appear. Right click on the cell, click on View more cell actions > Data Validation. From here, the criteria will be List from a range. Next, click on the four squares in the next field so that you can select the range from the Presidents tab.

Step 3: Setup the INDEX MATCH Formula

The formula is the same whether you're using Excel or Google sheets. Let's break it down.

In the first portion of the formula, like the data validation field, we need to pull in the "index" of data. Meaning, where do we want to pull the data from.

Let's use the next part of our table for the first example, we want to pull the list of Vice Presidents and then match the Vice President with the president.

However, since we have each selection of data separated into different tabs on our spreadsheet, we need a way to match each portion of data to the president. Since the main data point is President and we're matching all the other data accordingly, we need to add the President column to each tab of the sheet. Like this:

To write the first formula for Vice President, we will start with

=INDEX('Vice Presidents'!B:B)

This is saying that we can to bring in the INDEX of data from column B on the Vice Presidents tab. Next, we'll add the MATCH portion to the spreadsheet:

=INDEX('Vice Presdients'!B:B, MATCH(C3,'Vice Presidents'!A:A,0))

In the MATCH section, here is what each portion represents

C3	This is the dropdown field that we created in Step 2 of this guide.
'Vice Presidents'!A:A	This indicates that you want to match the present selected in the dropdown (C3) on Vice Presidents tab.
0	This is for the match type. Zero indicates that you want exact match results.