## Merge two related tables

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

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

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

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

Search two related tables simultaneously [VBA]

Let's say you do a lot of searches in two tables. The tables are related so it would be great […]

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

Paste image link to your comment.

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