INDEX MATCH across Multiple Sheets in Excel (With Alternative)

In Microsoft Excel, it’s a common scenario to lookup and then extract data from multiple sheets based on different criteria. The combination of INDEX and MATCH functions is a suitable method that can serve the purpose of pulling out data from multiple sheets into a particular one.

In this article, you’ll get to learn how we can use INDEX and MATCH functions across multiple worksheets with appropriate illustrations.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Use of INDEX MATCH Functions across Multiple Sheets in Excel

In the following picture, you can see multiple worksheets open in a single workbook. The first sheet has been named as Summary. In this sheet, the sales of a particular device or component on a particular date will be extracted from other corresponding worksheets.

use of index match across multiple sheets in excel

Below is the screenshot of the second worksheet named Notebook where the sales of notebooks on some successive dates have been recorded. Similarly, if we go through the rest of the worksheets available then we’ll find the sales of other devices or components- Desktop, Monitor, Processor, and Motherboard.

What we’ll do now is in the Summary sheet, we’ll extract the sales of notebooks on 1-Sep-2021 from the Notebook sheet.

use of index match across multiple sheets in excel

📌 Step 1:

➤ In the Notebook worksheet, select the entire table first.

➤ From the Styles group of commands under the Home ribbon, select any table you prefer from the Format as Table drop-down.

use of index match across multiple sheets in excel

📌 Step 2:

➤ Go to the Formulas tab and select the Name Manager command from the Defined Names drop-down.

use of index match across multiple sheets in excel

📌 Step 3:

➤ Edit the name of the table here and type Notebook in the Name box.

➤ Press OK.

use of index match across multiple sheets in excel

📌 Step 4:

➤ Similarly, follow the previous step for all other worksheets and name the corresponding tables with the device or components present in the Summary sheet.

➤ Close the Name Manager dialogue box and you’re now ready to assign the formula in the Summary sheet.

use of index match across multiple sheets in excel

📌 Step 5:

➤ In the first output Cell D5, type the following formula:

=INDEX(INDIRECT(B5&"[Sales]"),MATCH(Summary!$C5,INDIRECT(B5&"[Date]"),0))

➤ Press Enter and you’ll get the sales value of notebooks on 1-Sep-2021.

use of index match across multiple sheets in excel

📌 Step 6:

➤ Now use Fill Handle to fill down the rest of the cells in Column D.

Finally, you’ll be displayed the sales of other components or devices on the specified dates. If you change a date for any device in Column C, you’ll find the sales value of the particular device on that specified date at once. Similarly, you can change the device name too in Column B and you’ll be shown the corresponding sales value on the particular date.

use of index match across multiple sheets in excel


Alternative (VLOOKUP) to the Use Instead of INDEX MATCH Functions across Multiple Sheets

There is a suitable alternative to the INDEX and MATCH functions and that is the VLOOKUP function. The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a specified column.

Since we’re using the previous dataset, let’s have a look at how we can apply the VLOOKUP function in the output Cell D5 now. The required formula is:

=VLOOKUP($C5,INDIRECT("'"&B5&"'!$B$5:$C$10"),2,FALSE)

After pressing Enter only, you’ll get the first output as found in the previous method.

alternative to the index match across multiple columns in excel vlookup

Now use the Fill Handle option to autofill the rest of the output cells in Column D and you’ll be displayed the corresponding sales values right away.

alternative to the index match across multiple columns in excel vlookup


Concluding Words

I hope these two methods mentioned above will now help you to apply them in your Excel workbook to lookup and extract data from multiple worksheets. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

VLOOKUP from Another Sheet in Excel

VLOOKUP with 2 Conditions in Excel

Lookup and Return Multiple Values Concatenated into One Cell in Excel

VLOOKUP to Return Multiple Values Horizontally in Excel

VLOOKUP Max of Multiple Values (With Alternative)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo