## Merge two relational data sets

This article demonstrates how to merge two relational data sets before creating a Pivot table. A Pivot Table is limited to one table (data source) when you are working with relational data and I want to calculate the sales figures for each salesperson.

The image above shows one Excel table to the right that contains the product, price, and the company. The Excel Table to the left shows the salespersons and the corresponding company, a salesperson may work with multiple companies.

#### Table of Contents

## 1. Create Excel Tables

I highly recommend Excel Tables, they save you a lot of time if you need to add more data to your data set and they have other useful features as well. You don't need to update the cell references each time you add data, however, you still need to refresh the Pivot Table.

- Select all cells you want to convert to an Excel Table.
- Press CTRL + T to create an Excel Table, a dialog box is displayed.
- Enable the checkbox if the Table has headers.
- Press with left mouse button on OK button.

Excel applies automatically cell formatting to a new Excel Table, you can change the Table style if you don't like it.

Select any cell in the Excel Table, go to tab "Table Design" on the ribbon and select a new Tables Style if you prefer something else.

## 2. Add a column to an Excel Table

Excel expands the Excel defined Table automatically if you enter a value in an adjacent cell. Let's look for data in the first table [Table1].

- Select cell G2 which is the adjacent cell in this case, see image above.
- Type or copy/paste the following formula:
=INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))
- Press Enter.

Another great feature is that the Excel defined Table automatically copies formulas to all cells in a column which you will see after you pressed Enter in the third step above.

### 2.1 Explaining formula

The formula you entered in cell G2 is actually better than a VLOOKUP formula, it allows you to search any column and the column you return values from is not hardcoded into the formula which may be a problem if you insert more columns to a data table.

#### Step 1 - Find relative position

The MATCH function has three arguments, the first argument is a cell reference to a cell on the same row in column Company as the cell you are currently adding the formula to. [@Company] It is called a structured reference and is special to Excel Tables.

[@Company] does not contain a reference to a Table name, the formula is located in the same Table so the Table name is not needed.

The second argument is a structured reference to all values in column Company in Table1 Table1[Company] and the third argument 0 (zero) tells Excel to perform an exact match.

MATCH(*lookup_value*, *lookup_array*, *[match_type]*)

becomes

MATCH([@Company], Table1[Company], 0)

becomes

MATCH("MARS Industries", {"MARS Industries"; "The Lanford Lunch Box"; "Minuteman Cafe"; "Springfield Beans"; "Smith and Co."; "Sixty Second Avenue"; "Cyberdyne Systems"; "Osato Chemicals"; "Klimpys"; "Corellian"}, 0)

and returns 1. "MARS Industries" is in the first position in the array.

#### Step 2 - Get value

The INDEX function returns a value from a given cell range or array based on a row and column number. The column number is optional.

INDEX(*array*, *[row_num]*, *[column_num]*)

becomes

INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))

becomes

INDEX(Table1[Salesperson], 1)

and returns the first value from column Salesperson in Table1 which is "Kirti Whitt".

The Excel Table has now 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

## 3. Create Pivot Table

If you are new to Pivot Tables don't freak out, they are so useful that I recommend you take time to get to know them better.

A Pivot Table allows you to quickly create totals based on conditions you specify, there is no need to build complicated formulas.

The speed the Pivot Table runs tasks is incredible. It is one of the greatest built-in features in Excel, in my opinion.

- Select any cell in Table2, the table to the right.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Pivot Table" button and a dialog box is diplayed, see image below.

- Press with left mouse button on OK button. This will create an empty Pivot Table in a new worksheet.

## 4. Set up Pivot Table

The image above shows a Pivot Table to the left and the corresponding task pane to the right. The task pane contains a list of fields based on headers in your Excel Table, you can drag these fields to different areas below which are:

- Report Filter
- Column Labels (horizontally)
- Row Labels (vertically)
- Values

Follow the simple instructions below see how much each salesperson has sold and to what company.

- Drag
*Price*to Values area. - Drag
*Company*to Row Labels area. - Drag
*Salesperson*to Column Labels area.

The Report Filter lets you examine the data even deeper, simply drag a field to the Report Filter and a drop down list shows up above the Pivot Table. The drop down list contains items you may want to use as a filter condition.

### Related tables category

In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]

I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]

Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]

This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]

This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]

I will in this article demonstrate how to search a table for a date based on a condition and then […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 7 Responses to “Merge two relational data sets”

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