Joining Two Tables in Excel: A Practical Example with INDEX and MATCH

The basic theory of joining two tables is to match the values in a common column (also called a key column) and return the corresponding values from another column. For example, if you have two tables that contain product information and sales data, and they both have a column for product ID, you can join the tables by matching the product IDs and returning the product names, prices, quantities, etc.

There are different ways to join two tables in Excel using formulas, but one of the most common and versatile methods is to use the INDEX and MATCH functions. The INDEX function returns a value from a specific row and column in a table, and the MATCH function returns the position of a value in a column or row. By combining these two functions, you can look up a value in the key column of one table and return the value from another column in the same or another table.

The general formula for joining two tables with INDEX and MATCH is:

=INDEX (return_range, MATCH (lookup_value, lookup_range, 0), [column_num])

Where:

• return_range is the range of cells that contains the values you want to return. It can be in the same table or in another table.
• lookup_value is the value you want to match in the key column. It is usually a cell reference from the first table.
• lookup_range is the range of cells that contains the key column. It can be in the same table or in another table, but it must have the same number of rows as the return_range.
• column_num is an optional argument that specifies the column number in the return_range from which to return a value. If omitted, it defaults to 1.

To illustrate how this formula works, let’s create a scenario and give a detailed example with real numbers.

Suppose you have two tables that contain product information and sales data, as shown below:

You want to join these two tables and create a new table that shows the order ID, product name, quantity, price, and total amount for each order, as shown below:

To do this, you can use the following steps:

1. Create a new worksheet and name it “Joined Table”.
2. In cell A1, enter “Order ID” and copy the values from the Order ID column of the second table.
3. In cell B1, enter “Product Name” and use the INDEX and MATCH formula to return the product names from the first table. For example, in cell B2, enter the following formula:

=INDEX (Table1[Product Name], MATCH (A2, Table1[Product ID], 0))

This formula looks up the value in cell A2 (P001) in the Product ID column of the first table (Table1[Product ID]) and returns the position of the match (1). Then, it returns the value from the Product Name column of the first table (Table1[Product Name]) in the same row (Laptop).

4. Copy the formula down to fill the rest of the column.
5. In cell C1, enter “Quantity” and copy the values from the Quantity column of the second table.
6. In cell D1, enter “Price” and use the INDEX and MATCH formula to return the prices from the first table. For example, in cell D2, enter the following formula:

=INDEX (Table1[Price], MATCH (A2, Table1[Product ID], 0))

This formula works in the same way as the previous one, but returns the value from the Price column of the first table instead of the Product Name column.

7. Copy the formula down to fill the rest of the column.
8. In cell E1, enter “Total” and use a simple multiplication formula to calculate the total amount for each order. For example, in cell E2, enter the following formula:

=C2*D2

This formula multiplies the quantity in cell C2 (2) by the price in cell D2 (500) and returns the result (1000).

9. Copy the formula down to fill the rest of the column.
10. Format the table as desired, such as applying borders, alignment, number format, etc.

The final result should look something like this:

This is one way to join two tables in Excel using formulas. There are other approaches that you can use, such as:

• Using VLOOKUP or XLOOKUP functions to look up values from one table and return them to another.
• Using Power Query to merge queries and join tables with different types of joins, such as inner, left, right, or full outer join.
• Using Merge Tables Wizard, an add-in for Excel that allows you to combine tables by column headers or key columns with a few clicks.