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:
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:
Click on New and then select the tables and the field to use as the relationships between them:
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”:
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:
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: