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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar