Posted by Johan Vermeire on 04/07/2012.

You all know the situation where you receive a huge amount of data that contains a column with a product number. As the product number isn’t always meaningful, you add a second column and you use a vlookup to retrieve the product description from another table.  Then, you start creating PivotTables and you use the product description field in your analysis. But did you know that you can avoid this vlookup using PowerPivots?

Suppose that you have the following tables:

If you want to know the total amount that was sold per product, most people would add a new column to the sales table and use a vlookup-function to retrieve the description of the product.
Then, you’ll create a PivotTable to show per product the total amount.

In PowerPivot, you don’t need to add a column to the sales table.  Follow these steps:

1. Link both tables to PowerPivot
2. Instead of a vlookup-function, in PowerPivot we’ll create a relationship between the 2 fields, just as you would do in a relational database. Right-click on the ‘Productnr’ header of the sales table and select the option ‘Create Relationship’
   
3. A new window pops up where you can tell PowerPivot which 2 fields need to be connected. Compared with a vlookup, it will be the field that contains the lookup value (argument 1 of a vlookup) and the first column of the table that you would have selected as the second argument in the vlookup. In this example, we connect the productID of the productstable to the Product of the sales table:
   
   
4. Notice the icon in the title which indicates that the field is related to another field.
   

Now that PowerPivot knows how it can jump from one table to another table, let’s create the PivotTable:

1. Click on ‘PivotTable’ and place the PivotTable on a blank sheet. Notice that the field list shows all the tables of the PowerPivot window and the fields.
2. Drag the ProductID and the description from the products table to the row labels.
3. Drag the Amount of the sales table to the values section.
4. Optionally, you can remove the subtotals and modify the layout of the PivotTable to increase the readability.
   
   
   

Although it may not look that spectacular, notice that a vlookup has the following disadvantages:

• Excel will always look for the matching value in the first column of your selection. In PowerPivot you link the fields, so the order of the fields doesn’t matter.
• In a vlookup, you have to specify the column number of the table that contains the results that you want tot see. If someone inserts or deletes a column between the first column and the column that you need, the vlookup will give incorrect results.
•  If you have lots of data, a lot of vlookups need to be calculated which might slow down Excel.
• If you need addition fields (e.g.: the price per unit), you will need to create a vlookup for every column. In PowerPivot, you’ll just drag the desired field to the PivotTable.
• If you would lookup up the text "ABC " in a table where you have the value "ABC", a vlookup won't find a match because of the extra space after C. In PowerPivot, the 'ABC' value will be found.