In this article, we will learn how to use the VLOOKUP function to look up values from two different tables and add them together. This is useful when you have data in separate worksheets or workbooks and you want to perform calculations based on matching criteria.
The VLOOKUP function in Excel allows you to search for a value in the first column of a table and return a corresponding value from another column. The syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
where:
- lookup_value is the value you want to find in the first column of the table.
- table_array is the range of cells that contains the table of data.
- col_index_num is the column number in the table from which you want to return a value.
- range_lookup is an optional argument that specifies whether you want an exact match (FALSE) or an approximate match (TRUE).
To add between two VLOOKUP, you need to use two VLOOKUP functions in the same formula and add them together. For example, suppose you have two tables of sales data for different products and regions, as shown below:
![image]
You want to find the total sales for a specific product and region by adding the values from both tables. To do this, you can use the following formula:
=VLOOKUP(G2,A2:B6,2,FALSE)+VLOOKUP(G2,D2:E6,2,FALSE)
where:
- G2 is the cell that contains the product name you want to look up.
- A2:B6 is the range of the first table.
- D2:E6 is the range of the second table.
- 2 is the column number that contains the sales values in both tables.
- FALSE is the range_lookup argument that indicates you want an exact match.
This formula will return the sum of the sales values from both tables for the product in G2. You can copy the formula down to get the results for other products.
Procedures
To create a formula that adds between two VLOOKUP, follow these steps:
- Identify the lookup value, the table arrays, and the column index numbers for both VLOOKUP functions.
- Enter the first VLOOKUP function in a cell, using the appropriate arguments.
- Type a plus sign (+) after the first VLOOKUP function.
- Enter the second VLOOKUP function, using the appropriate arguments.
- Press Enter to complete the formula and return the result.
- Copy the formula to other cells if needed.
Explanation
To understand how the formula works, let’s break it down into its components. The formula consists of two VLOOKUP functions that are added together:
=VLOOKUP(G2,A2:B6,2,FALSE)+VLOOKUP(G2,D2:E6,2,FALSE)
The first VLOOKUP function looks up the value in G2 (Cookies) in the first column of the first table (A2:B6) and returns the corresponding value from the second column (B2:B6), which is the sales value for Cookies in the first table ($200).
=VLOOKUP(G2,A2:B6,2,FALSE)
The second VLOOKUP function looks up the same value in G2 (Cookies) in the first column of the second table (D2:E6) and returns the corresponding value from the second column (E2:E6), which is the sales value for Cookies in the second table ($300).
=VLOOKUP(G2,D2:E6,2,FALSE)
The formula then adds the two values returned by the VLOOKUP functions and returns the result ($500), which is the total sales for Cookies from both tables.
=VLOOKUP(G2,A2:B6,2,FALSE)+VLOOKUP(G2,D2:E6,2,FALSE) = $500
Example
To illustrate the use of the formula, let’s consider a scenario where you have two worksheets that contain sales data for different products and regions. The first worksheet (Sheet1) has the sales data for the North region, and the second worksheet (Sheet2) has the sales data for the South region, as shown below:
![image]
You want to create a summary report on a third worksheet (Sheet3) that shows the total sales for each product from both regions, as shown below:
![image]
To do this, you can use the following formula in cell B2 on Sheet3:
=VLOOKUP(A2,Sheet1!A2:B6,2,FALSE)+VLOOKUP(A2,Sheet2!A2:B6,2,FALSE)
where:
- A2 is the cell that contains the product name you want to look up (Cookies).
- Sheet1!A2:B6 is the range of the first table on Sheet1.
- Sheet2!A2:B6 is the range of the second table on Sheet2.
- 2 is the column number that contains the sales values in both tables.
- FALSE is the range_lookup argument that indicates you want an exact match.
This formula will return the sum of the sales values from both tables for the product in A2 (Cookies). You can copy the formula down to get the results for other products.
Other approaches
Besides using the VLOOKUP function, there are other ways to add between two tables in Excel. Here are some alternatives:
- Use the SUMIF function to sum the values based on a single criterion. For example, the following formula will return the same result as the VLOOKUP formula:
=SUMIF(Sheet1!A2:A6,A2,Sheet1!B2:B6)+SUMIF(Sheet2!A2:A6,A2,Sheet2!B2:B6)
- Use the SUMIFS function to sum the values based on multiple criteria. For example, the following formula will return the same result as the VLOOKUP formula:
=SUMIFS(Sheet1!B2:B6,Sheet1!A2:A6,A2)+SUMIFS(Sheet2!B2:B6,Sheet2!A2:A6,A2)
- Use the INDEX and MATCH functions to look up values from multiple tables and add them together. For example, the following formula will return the same result as the VLOOKUP formula:
=INDEX(Sheet1!B2:B6,MATCH(A2,Sheet1!A2:A6,0))+INDEX(Sheet2!B2:B6,MATCH(A2,Sheet2!A2:A6,0))
