## Merge two related tables

*Article updated on July 29, 2017*

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**

How to create a dynamic pivot table and refresh automatically

David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]### 7 Responses to “Merge two related tables”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Or you could simply use PowerPivot or the Excel 2013 DataModel, and build a pivot from two linked tables.

Edouard,

I forgot to mention that!

Hi Oscar,

I failed to find right article in your blog and therefore I want to ask you in newest post. So I have table similar like this:

A 5

B 2

C 1

D 4

Is it possible with formula to generate list like this:

A

A

A

A

A

B

B

C

D

D

D

D

Thank you in advance!

BatTodor,

Read this post: Repeat values

[...] BatTodor asks: [...]

You are genius Oscar! :)

Thanks a lot!

Best regards

Todor

Hi Oscar,

I got two pivot tables from two sets of data ie Pivot table1 from data set1 and pivot table from data set2. I would like to know whether it is possible to control these pivot tables with a single slicer. The two data sets have common fields.