Lookup values across multiple worksheets with dynamic sheet name
In many cases, you may need to collect data across multiple worksheets for summary. With the combination of the VLOOKUP function and the INDIRECT function, you can create a formula to lookup specific values across worksheets with dynamic sheet name.
How to lookup value across worksheets with dynamic sheet name?
Supposing you have four worksheets containing sales for different salesmen in four seasons as the below screenshot shown, and now you need to collect the sales for all salesmen across these four worksheets and return them into a summary worksheet. Here is the solution step by step for you.
Generic formula
=VLOOKUP(lookup_value,INDIRECT("'"&sheet&"'!"&"range"),col_index,0)
Create the summary worksheet which contains the name of the salesmen and the worksheet names as the below screenshot shown.
1. Select a blank cell (in this case, I select C3), copy the below formula into it and press the Enter key.
=VLOOKUP($B3,INDIRECT("'"&C$2&"'!"&"B5:C11"),2,0)
Notes:
- B3 contains the name of the salesman that you will find the corresponding sales based on;
- C2 contains the value matching the sheet name you will pull data from;
- B5:C11 is the table range in the quarter sheets containing both the Name column and the Sales column;
- 0 here means that the VLOOKUP function will return an exact match. If an exact match value can’t be found, it will return a #N/A error value.
2. And then drag the Fill Handle right and down to get all results.
Related function
The VLOOKUP function
The Excel VLOOKUP function searches for a value by matching on the first column of a table and returns the corresponding value from a certain column in the same row.
The INDIRECT function
The Microsoft Excel INDIRECT function converts a text string to a valid reference.
Related formulas
Lookup values from another worksheet or workbook
If you want to lookup values from different worksheet or workbook, this tutorial will do you a favor.
Click to know more...
Excel dynamic worksheet or workbook reference
Supposing you have data with the same format across multiple worksheets or workbooks and need to get data from these worksheets or workbooks dynamically into another sheet. This article will help you quickly get it done.
Click to know more...
Vlookup formula – the shipping cost calculator
This article provides detailed steps to calculate shipping cost based on specified weight of an item in Excel.
Click to know more...
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
