Tip: Other languages are Google-Translated. You can visit the English version of this link.

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...


Excel Productivity Tools

300 Advanced Features Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 70% time.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Office Tab Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.