Skip to main content

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 AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations