VLOOKUP & INDEX MATCH in Excel

In Excel, it’s relatively easy to lookup values in one specific tab.

But how do you lookup values in multiple tabs using only one formula?

In this tutorial, I’ll show you different methods for achieving this.

One method uses VLOOKUP and direct worksheet and cell references.

The other approach uses INDEX & MATCH and Excel Table names and references.

The key here is that the INDIRECT function acts as the messenger that returns the correct sheet address in a dynamic way to the different lookup formulas.

Of course, you can apply this (indirect) method inside other formulas.

Just keep in mind that INDIRECT is a volatile function.

This means that it calculates independently to Excel’s formula dependency tree, which in turn can have an impact on performance for larger spreadsheets. 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).

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’”)`

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

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

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

1. Zoltan Juhasz March 13, 2018 at 5:32 pm - Reply

Hi, cool article thank you for the ideas.
There’s a type-o though in this final formula at the Index-Match solution:

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

In the first INDIRECT, the table header Invoiced Amount misses the [

Regards,
Zoltan

• Leila Gharani March 14, 2018 at 8:39 pm - Reply

Just corrected the formula. Thank you for pointing that out.

2. hyperbob August 30, 2018 at 7:54 pm - Reply

how would I use this if the worksheets are in a different workbook?

3. Kevin September 17, 2018 at 3:30 pm - Reply

This was awesome! Thank you!

4. khushboo December 20, 2018 at 8:16 pm - Reply

Can you show the function without using substitute,assuming both are equal?

5. Mitch December 22, 2018 at 3:59 am - Reply

I there a way to search multiple worksheets? Not just one at a time based on a selection in a cell. Trying to accomplish an index match style lookup and value return but over the entire work book.

• Leila Gharani January 3, 2019 at 11:59 am - Reply

Not really with Index Match – for that you need to have a organized data set. If the values are scattered, you can create a macro that loops through each sheet and returns the address of the cell and sheet the match is found – or return the value of the adjacent cell. Depends on what you’d like returned.

6. manish shivale December 22, 2018 at 11:25 am - Reply

since I have learned only Index and Match function and was thinking of switching to different Div. through IFS function and get the same result.

=IFS(B13=”Game Div.”,INDEX(Game_Div.[Invoiced Amount],MATCH(Summary!\$B\$11,Game_Div.[Date],0)),B13=”Productivity Div.”,INDEX(Productivity_Div.[Invoiced Amount],MATCH(Summary!\$B\$11,Productivity_Div.[Date],0)),B13=”Utility Div.”,INDEX(Utility_Div.[Invoiced Amount],MATCH(Summary!B11,Utility_Div.[Date],0)))

I know this look horrifying, but is made out of simple logic of index and match.

DO TELL ME HOW MAY THIS METHOD FAIL ME?

I still need to comphrehend Indirect and substitute

7. Deepa January 7, 2019 at 11:22 am - Reply

Good Explanation, really useful.

8. Ethan Hoover February 27, 2019 at 10:37 pm - Reply

I am on the current version of 365, and I copy the formula exactly how you’ve put it in my table and it continues to through errors. I am at a loss been working on this for 2 days now. So lost!

=VLOOKUP(CustomerName,’Customer List’!\$A\$2:\$N\$99,4,FALSE)

I have also done it through the table version as well as index and match. Still coming up with errors.

• Bryon Smedley April 7, 2019 at 8:16 pm - Reply

Without seeing your data, it is difficult to know exactly why your VLOOKUP formula is throwing out an error. Can you provide a bit more detail on how your data is structured?

Thanks – XelPlus Team

9. ASZ April 15, 2019 at 2:29 pm - Reply

I Need to fix formula to below to get result in sheet4
Example If I enter only 160011 should appear 1.8 as per ASA44
…. kindly help me

Sheet1 OPT NO. JOB ASV50

Sheet2 OPT NO. JOB ASA44

Sheet3 OPT NO. JOB AXV70

• Bryon Smedley May 5, 2019 at 11:18 am - Reply

Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

Microsoft Excel Tech Community

With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

The XelPlus Team

10. Rachelle April 18, 2019 at 5:11 pm - Reply

Instead of pulling one number from the other tabs, could you SUM a few cells from each tab?