This post demonstrates how to merge two related tables before creating a pivot table. A pivot table is limited to one table (data source) and I want to calculate the sales figures for each salesperson. The table to the right shows the product, price and the company.

The table to the left shows the salespersons and their companies. A salesperson may have multiple companies.

Merge two related tables before creating a pivot table

Add column to table

Let's look for data in the first table [Table1].

  1. Select cell G2
  2. Type this formula:
    =INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))
  3. Press Enter

Merge two related tables before creating a pivot table1

Now the table has the corresponding salesperson next to the company name.

Merge two related tables before creating a pivot table2

If you are looking for more examples on merging two data lists, check this post: Merge lists with criteria

Create pivot table

  1. Select a cell in the table to the right
  2. Go to tab "Insert"
  3. Click "Pivot table" button
    Merge two related tables before creating a pivot table3
  4. Click Ok

Set up pivot table

Merge two related tables before creating a pivot table4

  1. Drag Price to Values area
  2. Drag Company to Row Labels area
  3. Drag Salesperson to Column Labels area

Download excel file

Merge two related tables.xlsx