Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Use VLOOKUP Across Multiple Worksheets

VLOOKUP is one of the most commonly used functions for looking up values in Excel. To perform VLOOKUP across multiple sheets in Excel, you can use a combination of Consolidate and VLOOKUP. 

Use VLOOKUP across multiple worksheets

If you want to use VLOOKUP across several worksheets in Excel, you can accomplish this by using the Consolidate feature as well as certain features of the VLOOKUP function itself. 

Use Consolidate in Excel with VLOOKUP

Consolidate is an Excel feature that helps you combine your data from multiple sheets into a single master worksheet. After consolidating the data, VLOOKUP can be used to look for matches within the master worksheet.VLOOKUP uses following the syntax: 
=VLOOKUP(value, table_array,col_index,[range _lookup]).
Generally, you can use it to look up one sheet at a time. However, modifying the data allows us to use VLOOKUP across multiple sheets in Excel.

In the following example, we have the sales record of beverages in three different states for the 1st quarter of the year 2018. The workbook contains three sheets of data for sales during the 1st quarter of the year. There are three items: LemonadeOrange Juice, and Pina Colada. The data contains the sale records for these items during the months JanuaryFebruary, and March. It represents the sales for three states, PH, TX and ID in the same cells B2:F5 for all three worksheets.

If you want to calculate the percent of individual items for any three months of the 1st quarter, you would need to extract the values with VLOOKUP and sum them. But VLOOKUP won’t work here as the sales records are laid out over multiple worksheets.

In this tutorial, we will show you how to use VLOOKUP when the data for table_array is spread over multiple sheets. You will find the percentage of total sales for Orange Juice sales during the month February.

Using VLOOKUP with reference data on multiple sheets

To use VLOOKUP with referenced data on multiple sheets, you will first consolidate the data on a master sheet. Then on the master sheet, a VLOOKUP formula will help to perform the correct calculation. For this to work you would need to follow the steps below:

  1. Create a new worksheet named “Qtr. 1 Overall” using the “+” icon on the bottom.
  2. Click on the cell where you want the consolidated data to begin. For consistency with the previous cells, you want this cell to be B2. Select B2 by clicking on it. Now, click Data > Consolidate.
  3. In the Function box, select the function SUM. In the reference box, first click on the “Qtr. 1 PH” sheet and select cells B2:F5 dragging them with your mouse. Click Add. Click on the “Qtr. 1 TX” sheet. At this point, Excel will automatically have the cells B2:F5 selected. If not, select cells B2:F5 and click Add. Repeat the same process for sheet “Qtr. 1 ID”.

  4. Click the “Top Row” and “Left Column” checkboxes. If you want the consolidated data to be updated automatically every time the sales sheets updates, click on the “Create links to source data” box.
  5. Click OK.

Excel will fill up the newly created worksheet with the sum of all items for the corresponding months. Now, you will make two more changes to the sheet. To add the changes perform the following steps:

  1. In cells B9 and C9 add the headers “Item Name” and “Month”. These will be the values returned by VLOOKUP. Add the header “Total” in cell D9. This would be the total calculated by consolidation and returned by VLOOKUP.
  2. In cells B10 and C10, add the name and month “Orange Juice” and “Feb.” On cell D10 insert the formula “=VLOOKUP(B10,$B$3:$F$5,2,FALSE)/VLOOKUP(B10,$B$3:$F$5,5)“
  3. Select cell D10, format it as “percentage.” To do this click home > % (On the Number section in the middle).

This will return 34% as the percent of total sales for “Orange Juice” for the month of February over the three states.

If you have trouble with using VLOOKUP and want to save hours of researching, try our Excel Chat live help service. Our experts are available 24/7 and ready to answer any Excel related question on the spot. The first question is free.

Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have a pivot table in which per order (on the rows) stands how much products they ordered per size (on the columns). I want to determine which combinations of quantities of sizes people order. And I want to count these combinations.
Solved by E. W. in 60 mins
I have 500 numbers in column A with aproximatly 15digits. In column B I have 5 numbers with 6 digits Can a find a formula wich can transfer all numbers from A column wich start with some 6 digitsa from B column
Solved by V. L. in 32 mins
I have a sheet with 3 columns. First column is a code for records in column B (has 563 records). What I need is a formula to show me if what is in column C (has 4400 records) is in column B and if it is to take the code for that record. Example: Column A(code corresponding to column B): 12, 14, 15, 19 Column B(names): asd, adf, ade, aqw Column C(names): akd, adf, ade, anb, wgs I need something that would say, if record in column C (for example adf) is matching record in column B (adf) return the code from column A (the code corresponding to that record in column B),if not return blank.
Solved by C. J. in 8 mins
If the data in A matches the data in C, then I need the data in E to be placed in B
Solved by X. W. in 40 mins
I have a workbook with 2 sheets. First sheet has column A which is a device name, then columns B - infinity that have numeric "tags" for that device. Each device can have one or 20 "tags", tags are all numerical. Worksheet 2 has column A which is numeric value and B which is a human readable value of what the tag is. Question is, how do I replace all the tag numbers in worksheet 1 with the actual human readable values is sheet 2?
Solved by T. H. in 16 mins

Leave a Comment


Subscribe to

Get updates on helpful Excel topics

Subscribe to

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc