## Merge two related tables

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

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Follow hyperlinks in a pivot table

Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

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 […]

Change pivot table data source using a drop down list

In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using […]

Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes […]

Working with three related tables

I have written a few posts about two related tables and today I am going to show you how to […]

Extract unique distinct values from a related table

In a previous post I described how to do lookups in a related table. In this post I am going […]

Lookups in a related table (array formula)

The "new" excel 2010 powerpivot feature and DAX formulas lets you work with multiple tables of data. You can connect […]

Use Conditional Formatting to do lookups in related tables

It can be really hard trying to follow a lookup in related tables. I will show you how to use […]

### 7 Responses to “Merge two related tables”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.