How to Combine Data from Two Tables in Excel Formula

Sometimes, you may need to combine data from two tables in Excel, such as when you have a list of products and a list of sales, and you want to calculate the total revenue for each product. There are different ways to do this, but one of the most common and powerful methods is using the VLOOKUP function.

What is VLOOKUP?

VLOOKUP is a function that allows you to look up a value in one table and return a corresponding value from another table. The syntax of the function is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

where:

• lookup_value is the value that you want to find in the first column of the table array.
• table_array is the range of cells that contains the data you want to retrieve. The first column of the table array must contain the lookup values.
• col_index_num is the number of the column in the table array that contains the value you want to return. The first column is 1, the second column is 2, and so on.
• [range_lookup] is an optional argument that specifies whether you want an exact match or an approximate match. If you omit this argument or set it to TRUE, the function will return an approximate match, which means it will find the closest value that is less than or equal to the lookup value. If you set this argument to FALSE, the function will return an exact match, which means it will find the value that is exactly equal to the lookup value. If there is no match, the function will return an error.

How to Use VLOOKUP to Combine Data from Two Tables

To use VLOOKUP to combine data from two tables, you need to follow these steps:

1. Arrange your data in two tables, each with a unique identifier in the first column. For example, you may have a table of products with product ID, name, and price, and a table of sales with product ID, date, and quantity.
2. Decide which table you want to use as the main table and which table you want to use as the lookup table. The main table is the one that contains the data you want to combine, and the lookup table is the one that contains the data you want to retrieve. For example, you may want to use the table of products as the main table and the table of sales as the lookup table.
3. In the main table, insert a new column where you want to display the combined data. For example, you may want to insert a new column after the price column to show the total revenue for each product.
4. In the new column, enter the VLOOKUP formula, using the unique identifier as the lookup value, the lookup table as the table array, and the column number that contains the data you want to return as the col_index_num. For example, if you want to return the quantity sold for each product, you can use the formula:

=VLOOKUP(A2, Sales!A:C, 3, FALSE)

where A2 is the product ID in the main table, Sales!A:C is the range of cells that contains the lookup table in another worksheet named Sales, 3 is the column number that contains the quantity in the lookup table, and FALSE is the range_lookup argument that indicates an exact match.

5. Copy the formula down the column to fill the rest of the cells. The function will look up the product ID in the lookup table and return the corresponding quantity. If there is no match, the function will return an error.
6. To calculate the total revenue for each product, you can multiply the price and the quantity in another column. For example, you can use the formula:

=C2*D2

where C2 is the price and D2 is the quantity in the main table.

7. Format the cells as desired, such as applying currency or percentage formats, or adding borders or colors.

Example

Here is an example of how to use VLOOKUP to combine data from two tables in Excel. Suppose you have the following two tables in two worksheets named Products and Sales:

You want to combine the data from these two tables and calculate the total revenue for each product. To do this, you can follow these steps:

1. In the Products worksheet, insert a new column after the Price column and name it Quantity.
2. In cell D2, enter the formula:

=VLOOKUP(A2, Sales!A:C, 3, FALSE)

3. Copy the formula down the column to fill the rest of the cells. The function will look up the product ID in the Sales worksheet and return the corresponding quantity. If there is no match, the function will return an error. The result should look like this:

4. Insert another column after the Quantity column and name it Revenue.
5. In cell E2, enter the formula:

=C2*D2

6. Copy the formula down the column to fill the rest of the cells. The function will multiply the price and the quantity to calculate the total revenue for each product. The result should look like this:

7. Format the cells as desired, such as applying currency format to the Price and Revenue columns, or adding borders or colors.

Other Approaches

VLOOKUP is not the only way to combine data from two tables in Excel. There are other approaches that you can use, such as:

• Using the INDEX and MATCH functions, which can perform a similar lookup operation as VLOOKUP, but with more flexibility and accuracy. For example, you can use the formula:

=INDEX(Sales!C:C, MATCH(A2, Sales!A:A, 0))

to return the quantity for each product ID, where Sales!C:C is the column that contains the quantity in the lookup table, Sales!A:A is the column that contains the product ID in the lookup table, and 0 is the match type that indicates an exact match.

• Using the SUMIF or SUMIFS functions, which can sum up the values in one table based on the criteria in another table. For example, you can use the formula:

=SUMIF(Sales!A:A, A2, Sales!C:C)

to return the total quantity for each product ID, where Sales!A:A is the column that contains the product ID in the lookup table, A2 is the product ID in the main table, and Sales!C:C is the column that contains the quantity in the lookup table.

• Using the Power Query feature, which can import, transform, and merge data from different sources, such as other worksheets, workbooks, or external files. You can use the Power Query Editor to create queries that can combine data from two tables using various methods, such as joining, appending, or merging. You can then load the query results to a new worksheet or table.