How To Use Relationships Instead Of VLOOKUP In With PivotTables In Excel

Here is how you can use Relationships and Data Modelling instead of VLOOKUP in Excel:

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

I have three tables of data and I want to create one PivotTable that uses information from all three. One approach would be to use VLOOKUP and add additional columns to the primary table, but instead we are going to use Relationships in Data Modelling to create a link between the three.

The three tables are Sales, City, and Territory:

rl1

The links we are going to create will use the Model field between the Sales and City tables, and the Salesman field between the Sales and Territory tables.

To do this we will go to the Data tab and click on Relationships in the Data Tools group:

rl2

Click on New and then select the tables and the field to use as the relationships between them:

rl3

Now, when you insert a PivotTable, choose “Use an external data source”, click on “Choose Connection…” and on the Tables tab, click on “Tables in Workbook Data Model” and the click “Open”:

rl4

Next you will see that in the Field List, all three tables appear and you can click on the toggle next to each to reveal the fields available:

rl5

You can drag any of the fields from the three tables as desired to create your PivotTable. Here we’ve taken four fields from three different tables to create one PivotTable without using any VLOOKUP functions:

rl6

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

4 Responses to How To Use Relationships Instead Of VLOOKUP In With PivotTables In Excel

  1. Ejaz Ahmed April 15, 2014 at 3:14 am #

    This is great. Will certainly come in Handy. Thanks for sharing, great video.

  2. John Young May 22, 2014 at 8:00 pm #

    Hello Michael

    Just a question relating to data modelling. Is this in excel 2010 or 2013. If 2013 is there a way to do this in 2010

Leave a Reply