Wondering how to use VLOOKUP and SUM across multiple sheets in Excel? We’ve got you covered right here.
VLOOKUP is a useful function in Excel that can help you in many ways, especially when dealing with a large data set. However, VLOOKUP is not only used to search for text on a sheet or across multiple sheets. It can also be used to consolidate numbers across sheets.
In this guide, we will explore how to use VLOOKUP and SUM across multiple sheets to manage data so you don’t have to do it manually.
Examples of VLOOKUP and SUM across multiple sheets
Scenario on hand: We have a sample dataset of L’Oreal sales across three months.
What we want to accomplish: We want to use VLOOKUP and sum to get a consolidated sales figure. We have 2 methods for this:
- Using SUM and VLOOKUP nested function
- Listing sheet names
Before we delve into the methods, let’s look at the data.
We have three sheets, namely “January,” “February,” and “March,” that include the sales figures for the said months.
On a separate sheet, we have the names of all the L’Oreal products:
Method 1: Using SUM and VLOOKUP nested function
The SUM formula is simple. Type =sum and enter the values or cell addresses for the values to sum.
However, when you have data on multiple sheets, summing them up requires VLOOKUP.
Here’s the formula you can use SUM and VLOOKUP together:
=SUM(VLOOKUP({Cell address of lookup value},{Sheet name}!{Table array with $},{Column index number} ,FALSE),VLOOKUP({Cell address of lookup value},{Sheet name}!{Table array with $},{Column index number},FALSE)
The VLOOKUP formula will be repeated for the number of sheets you need to search.
We used this formula for three sheets:
=SUM(VLOOKUP(B3,January!$C$6:$E$12,3,FALSE),VLOOKUP(B3,February!$C$6:$E$12,3,FALSE),VLOOKUP(B3,March!$C$6:$E$12,3,FALSE))
Here’s the result:
Note that we have used $ signs to enter the absolute value of the formula. This way, when you drag the formula across the table, the cell address for the lookup value will change, but the table array will be locked.
Here’s how to use the Fill Handle tool:
Put your cursor on this small square and, pressing the left button, drag it downwards:
Here’s the result of the formula applied across all the products:
The formula returns a #N/A value for some products because they are unavailable in all the sheets.
If you get the same error, you need to use the IFERROR function so the formula skips the sheet where it doesn’t find the value.
We added IFERROR to the first SUM value, i.e., the first sheet:
=SUM(IFERROR(VLOOKUP(B5,January!$C$6:$E$12,3,FALSE),0),VLOOKUP(B5,February!$C$6:$E$12,3,FALSE),VLOOKUP(B5,March!$C$6:$E$12,3,FALSE))
Note that we added a “0” at the end of the IFERROR formula, so it uses 0 in place of missing values instead of returning a “#N/A” error.
Upon expanding this formula across the table, we remove two #N/A errors:
This means that these two products were not present in the first sheet but were present in the rest of the two sheets.
We now add IFERROR to all three of the sum values. However, to apply multiple ISERROR functions in sum, we have to add a + sign in between the values.
Here’s the formula you can use:
=SUM(IFERROR(VLOOKUP({Cell address of lookup value},{Sheet name}!{Table array with $},{Column index number} ,FALSE),0))+(IFERROR(VLOOKUP({Cell address of lookup value},{Sheet name}!{Table array with $},{Column index number} ,FALSE),0))
Here’s how we applied this formula for adding sales across three sheets:
=SUM(IFERROR(VLOOKUP(B3,January!$C$6:$E$12,3,FALSE),0))+(IFERROR(VLOOKUP(B3,February!$C$6:$E$12,3,FALSE),0))+(IFERROR(VLOOKUP(B3,March!$C$6:$E$12,3,FALSE),0))
The formula in action:
Method 2: Listing sheet names
The above method is quite simple. But when you have a lot of data spread across multiple sheets, entering sheet names raises the chances of errors.
This method allows you to list the sheet names where you’re doing all your work and use it to fetch data.
For this method to work, first list the sheet names like this:
Then select the names of the sheets and head to the “Formulas” tab. Here, click the option “Define Name” and enter a suitable name:
Then, in the first cell of the quarterly sales column, enter this formula:
=SUMPRODUCT(SUMIF(INDIRECT(“‘”&{Defined sheets name}&”‘!${column array of product names with $}”),{Cell address of lookup value},INDIRECT(“‘”&{Defined sheets name}&”‘!{Column array of sales with $”)))
=SUMPRODUCT(SUMIF(INDIRECT(“‘”&Months&”‘!$C$6:$C$12”),B3,INDIRECT(“‘”&Months&”‘!$E$6:$E$12”)))
Things to Remember
- If you have a different dataset, follow the given syntax and change the cell values accordingly.
- Ensure the data is on the same cells across the sheets for the second method to work.
Wrapping up
You can use VLOOKUP combined with SUM to look and sum values across sheets or tables. This tutorial explored how you can do that. It also introduces the use of the IFERROR function with VLOOKUP and SUM to skip sheets where the formula doesn’t find the value. We also explore a listing method that makes summing across sheets easier.
Learn more about the VLOOKUP function in Excel through these guides:
