3 Easy Ways to Use INDEX-MATCH Across Multiple Sheets

Organizing data using various techniques is quite common in Excel. Records are arranged in a tabular form here. You can use different worksheets for the documentation. But, you might need to connect the information across multiple sheets. Addressing the data manually is not only tiresome but also inefficient. So, in cases of extensive datasets, you should use the INDEX-MATCH functions. This article will show you three ways to use INDEX-MATCH functions across multiple sheets. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.


📁 Download Excel File

Download the required Excel file below.


Learn to Use INDEX-MATCH Formula Across Multiple Sheets with These 3 Approaches

The article is going to introduce three approaches in Excel to use The INDEX function and the MATCH function across multiple Excel sheets. The methods will help you understand the uses elaborately. The following discussion will cover uses in a Table format, the Drop-Down method, and the Name Manager option. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.


approach

1. Using Table Format

Using one formula, you can update your records automatically. The method only requires you to format your source and result sheets as tables. The formula is a basic combination of the INDEX and the MATCH functions. Following is an example to update the name and score from one sheet to another automatically.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select the cell range of B5:C8 for the source sheet “Scores”.
  • Secondly, press Ctrl+T to enable the range as a table form. You can choose the Format as Table option from the Home tab as well. Choose any design from the styles.

  • You can see the table in the following picture.

Table form - INDEX MATCH across multiple sheets

  • Thirdly, repeat the table forming process for sheet “Table”.
  • Fourthly, on the output sheet “Table”, you need to bring scores from sheet “Scores”.
  • Fifthly, enter the following formula on cell C6.
=INDEX(Table_3[Score],MATCH([Name],Table_3[Name],0))
  • Hit the Enter key.
  • You will find the entire column of the table with the source data automatically.

🔨 Formula Breakdown

INDEX(Table_3[Score],MATCH([Name],Table_3[Name],0))

👉  Here, MATCH looks for an exact match for the table heading “Name” from “Table_3” with the same table heading.

👉  Also, INDEX returns an array from the “Table_3” “Score” column and receives row_num as the return from the MATCH function.

Finally, all the scores for the students are automatic.

📕 Read More: 6 Methods to Match Multiple Criteria from Different Arrays in Excel


approach

2. Utilizing Drop-Down List

This method makes the use of the INDEX and MATCH functions easier. It doesn’t need any formatting. Rather, works, brilliantly in cases of Drop-Down List data. Moreover, it can help you make only your required data visible along with all the details. Following is an example getting the test Paul attended and the score he received from separate sheets for test ID and scores.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, create a Drop-Down List option for cell C5. Here, the source cell range will be the sheet “Name List”.
  • As you can see from the picture below, there are two sheets after “Name List”. These are “Test List” and “Score List”.

Drop-down list - INDEX MATCH across multiple sheets

  • Next, on cell C6, enter the following formula.
=INDEX(‘Test List’!C5:C8,MATCH(‘Drop-Down’!C5,’Test List’!B5:B8,0))
  • Hit the Enter key.

  • Then, on cell C7, enter the given formula.
=INDEX(‘Score List’!C5:C8,MATCH(‘Drop-Down’!C5,’Score List’!B5:B8,0))
  • Hit the Enter key.
  • Finally, you will be able to update data according to the data that the drop-down list shows.

Drop-Down - INDEX MATCH across multiple sheets

  • As visible from the following image, you should get different data as you change the Drop-Down List from “Paul” to “Betty”.

🔨 Formula Breakdown

INDEX(‘Test List’!C5:C8,MATCH(‘Drop-Down’!C5,’Test List’!B5:B8,0))

👉  Here, MATCH looks for an exact match for the value of C5 from the sheet “Drop-Down” with values from cell range B5:B8 from sheet “Test List”.

👉  Also, INDEX returns an array from the “Test List” with cell ranging B5:B8 and receives row_num as the return from the MATCH function.

Finally, all the data for Paul is visible on the sheet.

📕 Read More: Use INDEX Function to Match and Return Multiple Values Vertically


approach

3. Employing Name Manager Option

The Name Manager is an important option to make use of the table format the most. With its help, you can modify your tables with a name and range. To access data in one sheet using the INDEX and the MATCH function across multiple sheets, the Name Manager option comes in handy as well. Both these functions are merged with the INDIRECT function to return the reference specified by a text string. Following is an example to access individual students’ mark sheets and finally arrange them according to tests.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, go to sheet “Lisa” and convert the dataset into the table as you did in the first method.

Table name - INDEX MATCH across multiple sheets

  • Repeat the action for all the students’ sheets “Paul”, and “Betty”.
  • Now, switch to the main result sheet “New Manager”.
  • Next, press Ctrl+F3 to get the New Manager dialogue box. You can choose the Name Manager option from the Defined Names drop-down, under the Formulas tab as well.
  • Then, cross-check three tables’ Refers To option to be sure about your dataset.
  • After that, rename your tables with the students’ names.
  • Click on the Close button.

  • Next, on cell D6, enter the following formula.
=INDEX(INDIRECT(B6&”[Score]”),MATCH(‘Name Manager’!$C6,INDIRECT(B6&”[Test ID]”),0))
  • Hit the Enter key.

Name Manager - INDEX MATCH across multiple sheets

  • Now, using Fill Handle icon, copy the formula down all the cells of the table.

🔨 Formula Breakdown

INDEX(INDIRECT(B6&”[Score]”),MATCH(‘Name Manager’!$C6,INDIRECT(B6&”[Test ID]”),0))

👉  Here, INDIRECT brings the specified text string from cell B6 within the column “Test ID”.

👉  Also, MATCH looks for an exact match for the value of C6 from the sheet “Name Manager”. Then it matches with values from the INDIRECT function.

👉  Again, INDEX returns an array from the INDIRECT function and matches it with the returns from the MATCH function.

Finally, all the data for three students for particular tests are visible on the final sheet.


📄 Important Notes

🖊️  While working on Name Manager, you should notice the range before naming the tables.

🖊️  Table formation is a mandatory step for the mentioned step.


📝 Takeaways from This Article

The article lets the readers understand the variety of options available to use the INDEX and the MATCH functions across multiple sheets.

📌  Primarily, you can use the functions on the Table formats easily. All you need to do is use a dedicated formula.

📌  You can combine the INDEX and the MATCH functions to work on the Drop-Down List option as well. It will get you an immediate update on your choice of data.

📌  Addition of the INDIRECT function with the aforementioned functions can enable more versatile operation of data combination across multiple sheets. You can work on individual tables.


Conclusion

To use the INDEX function and the MATCH function across multiple Excel sheets, three methods are useful. You can use the Table formation, Drop-Down List approach, and finally the Name Manager option. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.


Related Articles

(Visited 26 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo