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.
Add column to table
Let's look for data in the first table [Table1].
- Select cell G2
- Type this formula:
=INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))
- Press Enter
Now the table has the corresponding salesperson next to the company name.
If you are looking for more examples on merging two data lists, check this post: Merge lists with criteria
Create pivot table
Set up pivot table
- Drag Price to Values area
- Drag Company to Row Labels area
- Drag Salesperson to Column Labels area
Download excel file