As an example, we’ll take a report that has a Summary tab, and – based on the point of view in the Summary tab – you’ll want to retrieve information from different tabs.
In this example, the Summary tab contains three divisions and we want to retrieve the Invoiced amount from their respective tabs based on the Month selected on cell B4.
The data in the Division tabs are arranged in a simple way where it indicates the Invoiced amount for each month.
This layout is the same for all three tabs: Game Div., Productivity Div., and Utility Div.
Why not use the IF() function
It’s possible to use the IF() function here by setting it up to check if the Division is Game Div. then look in the Game Div tab, and Productivity Div tab if the Division is Productivity Div and so on.
The problem arises when more tabs are added to the report and you’ll have to revise all the nested IF() statements in the summary.
It would be optimal to have one single dynamic formula which you could use no matter how many tabs you have.
Solution 1: VLOOKUP approach using sheet names and cell references
To start simply, let’s write the basic VLOOKUP formula first.
We are also going to assume that Game Div is fixed and the report has just this tab.
Once the formula is set up, we can proceed to make the tab part dynamic as well. (Here is a link to a guide for VLOOKUP functions).
Start with cell C6.
The syntax for VLOOKUP() is:
= VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])- lookup_value: Since you know that you will be looking at the Game Div. tab, this does not need to be an argument. Lookup value is the cell containing the month, cell B4. Since you want to be able to pull this formula down, fix this cell reference to $B$4.
- table_array: Go to Game Div. and highlight the entire table and add a few more rows to include future data (‘Game Div.’!$A$4:$B$24)
- col_index_num: This tells which column to look at. In this case, since we want the second column in the table array area, we use 2.
- range_lookup: Select TRUE for an approximate match or false for an exact match. In this example, we want an exact match.
Cell C6 =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)
However, when this formula is pulled down to C8, we get the same value since our tab is fixed to Game Div..
In order to make the formula dynamic to account for the different Divisions, the table_array part of the formula should be revised.
The INDIRECT() function comes in handy here.
This function gets the right address from a cell or text reference. (Here is a link to a guide to using the INDIRECT() function.)
The syntax of the INDIRECT() function is:
= INDIRECT(ref_text,[a1])To understand how it works, the formula =INDIRECT(“A3”) tells the function to go to cell A3 and returns the value in cell A3, which is “Summary Report”.
In the same way, if you use another cell (E5) containing “A1” and use that cell as the ref_text in the formula
Cell E6 = INDIRECT(E5)it returns the value in cell A1.
The formula uses cell E5 as the address, which points the formula to cell A1.
This was our basic VLOOKUP formula:
=VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)Where does the INDIRECT function come in?
The first parameter, lookup_value, is fixed, as well as the col_index_num and range_lookup.
Since the table_array changes depending on the Division, the INDIRECT() function will be used here.
Before applying the INDIRECT() function, it’s worthwhile noting that when the tab names contain spaces, such as “Game Div.”, then any cell reference to that tab will need to include single quotation marks, e.g. ‘Game Div.’!.
The tab name can’t be replaced with a cell reference without putting it inside the INDIRECT() function since the INDIRECT() function will translate the tab name as the address for VLOOKUP().
As a first step, wrap the table_array argument of VLOOKUP inside the INDIRECT function.
INDIRECT(“’Game Div’!$A$4:$B$24’”)The full formula reads:
Cell C6 =VLOOKUP($B$4, INDIRECT(“’Game Div’!$A$4:$B$24’”),2,FALSE)As the INDIRECT() function is now included in the formula, all that’s left to do is to make the tab names dynamic.
Replace “Game Div.” with a cell reference and make sure to keep the single quotation marks.
The table_array parameter now becomes:
INDIRECT(“’”&B6&”’!$A$4:$B$24”)The & symbol is used to combine the single quotation marks and the content of cell B6.
The final formula becomes:
Cell C6 =VLOOKUP($B$4, INDIRECT(“’”&B6&”’!$A$4:$B$24”),2,FALSE)
Pull the formula down to C8 and it should now show the correct Invoiced amount values.
Solution 2: INDEX-MATCH approach using table names
This approach involves converting all the data in the Division tabs into Excel data tables.
Click on any data cell in the Division tab.
Press CTRL + T to display the Create Table window.
This will prompt you to specify the area of the data table.
This converts the data to an Excel data table.
To change the formatting of the table, click on any table cell and go to Design > Table Styles to select a scheme you prefer or Clear to revert back to the original.
Specify a Table Name under the Design tab.
Note that spaces are not allowed in Table Names so you might want to replace the spaces with underscores.
Do the same for the other two Divisions such that you have these table names:
Go back to the Summary tab and build the formula using the INDEX-MATCH approach. (Here is a link to a guide to using INDEX() and MATCH() functions.)
The syntax of the INDEX() function is:
= INDEX(array, row_num,[column_num])- array: This is the area where the answer is.
- row_num: How many rows it has to go down to find the answer.
- column_num: How many columns to the right it needs to go to find the answer.
As before, let’s start simply with the core formula first.
We will assume our only tab is Game Div.
Once the formula works for Game Div., we can expand on it to lookup values across the different tabs.
Start the formula by inputting:
Cell D6 = INDEX(
Go to the Game Div. tab and select the Invoiced Amount column.
Instead of displaying a cell reference, this will show now show as Game_Div.[Invoiced Amount].
The second parameter of the INDEX() function is the row_num.
Basically, that’s how many rows it needs to move down to find the answer.
Instead of hardcoding this, the MATCH() function is used to find what row corresponds to the Date selected in the Summary tab and return the position to the INDEX() function.
The syntax of the MATCH() function is:
=MATCH(lookup_value, lookup_array,[match_type])The Match formula becomes:
MATCH(Summary!$B$4!,Game_Div.[Date],0).The final formula becomes:
Cell D6 = INDEX(Game_Div.[Invoiced Amount],MATCH(Summary!$B$4, Game_Div.[Date],0))
Pulling this formula down to the Utility Div. row will return the same values since they are hard-coded to look inside the Game Div. tab.
To fix this, we will use the INDIRECT() function to help us get the dynamic tab names.
However, the naming convention in cells B6:B8 is different to that of the table names because the latter uses an underscore instead of the space.
We will need a formula that takes cells B6:B8 and converts them to look exactly like the corresponding table names—that formula needs to replace the space with an underscore.
The SUBSTITUTE () function will help us make this replacement.
As a first step let’s introduce the INDIRECT Function.
- Add the INDIRECT() function by replacing Game_Div. [Invoiced Amount] with INDIRECT(“Game_Div.[Invoiced Amount]”)
2. Replace Game_Div. with the cell reference B6 and combine it with the table header [Invoiced Amount] using the & symbol.
3. To replace the space with an underscore, use the SUBSTITUTE() function.
The syntax is SUBSTITUTE(text,old_text,new_text,[instance_num]).
- text: The cell you want the substitution to take place.
- old_text: What specific character you want to replace. In this case, it is “ “.
- new_text: What to substitute the old_text with. In this case, it is “_”.
- Instance_num: How many times we want the substitution to take place. This is an optional argument. We can leave it out which means we’d like all instances of “ “ to be replace with “_”.
=SUBSTITUTE(B6,“ ”,”_”)&”[Invoiced Amount]”…
4. The same substitution should be applied to the reference of the MATCH() part. The final formula now becomes:
Cell D6 = INDEX(INDIRECT(SUBSTITUTE(B6,“ ”,”_”)&“[Invoiced Amount]”),
MATCH(Summary!$B$4, INDIRECT(SUBSTITUTE(B6,“ ”, “_”&“[Date]”),0))
5. Drag the formula down to cell D8.
With these two methods, you can automatically lookup values from different tabs.
One version used cell references and sheet names and the second method used table names.
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
