Excel PowerPivot As VLOOKUP


Who doesn’t love to use VLOOKUP?  Using that formula saves both time and effort when you need to analyze data.

However, if you have thousands or more of VLOOKUP formulas, a performance hit to your computer’s CPU can happen. Not only that, the addition of one or more columns of data will be needed, depending on how many VLOOKUP formulas are used. Also, formulas can break when you add to, remove from or move columns or rows in the source data.

This is where using Excel PowerPivot as VLOOKUP will really help.

To use PowerPivot, you will need to enable the tab. If you don’t have the PowerPivot tab in your Excel, see this post to enable the tab.

First Step: Getting Data Into PowerPivot

PowerPivot needs Excel 2010 or newer. With PowerPivot, Excel tables, and database tables can become part of the PowerPivot.

There is more than one way to get the data into PowerPivot. The Excel data can be copied and pasted into PowerPivot as a table, or an Excel table can be linked to PowerPivot.

Copying and pasting will produce a static table in PowerPivot. However, linking an Excel table to a PowerPivot table will allow any changes made to the data in Excel to automatically be updated in PowerPivot.

Next Step: Using PowerPivot as VLOOKUP

In the example used for this post, what is wanted is a summary of the total amount per account code description. Yet, there are two separate tables of data – one with the account code description and the other without it.

One table has the sales information, with the account code, but no description of that account code that the sales are made to.

The second table has the account code and the description.

Typically, to get the description, a column would be added to the first table of data with a VLOOKUP formula to pull the description field from the second table of data.

However, by using PowerPivot as VLOOKUP, there will be no need to add a column or create a formula.

Below are the two tables from one spreadsheet that will be used for the PowerPivot.

powerpivot as vlookup

First Table to be Joined by PowerPivot

 

powerpivot as vlookup

Second Table to be Joined by PowerPivot

To link an existing Excel table to PowerPivot, click in the table and then on the PowerPivot tab, under Tables, click Add to Data Model.

powerpivot as vlookup

PowerPivot Tab Add to Data Model Button

After adding both tables to the data model in PowerPivot, select the home tab, and then click diagram view.

You will see that both table are now presented as diagrams. This diagram view look very similar to the Relationships view of data table in Microsoft Access. The same principles apply to creating relationships between the data in PowerPivot and the data in Access tables.

powerpivot as vlookup

PowerPivot Diagram View

Next, you will need to link the related fields in each table. In this case it is the ‘Account Code’ field. Click on the field name in either table and drag to the same field name in the other table.

powerpivot diagram view powerpivot as vlookup

PowerPivot Diagram View Showing Table Relationship

This creates a relationship between those two fields in both tables, effectively replacing the VLOOKUP formula, using PowerPivot as VLOOKUP.

Last Step: Create the Pivot Table

Now click on the home tab and the click PivotTable. This creates a pivot table using the tables you have just added and created a relationship between.

Notice that the fields in the pivot table are now grouped by table.

Add the description field from the account codes table to the rows area and add the total field from the expenditures table.

powerpivot as vlookup

PowerPivot Pivot Table Fields

Now the pivot table allows the viewing of the total per account code description. No columns or formulas were needed to be added to do this.

pivottable created powerpivot as vlookup

Pivot Table Created Using PowerPivot

Conclusion

PowerPivot is a very powerful tool that can be used for consolidation and reporting on large quantities of data.

This was a very simple demonstration with only only two tables. This same process can be repeated for more tables of data which wouild normally be used with more VLOOKUPS. This should help you think of ways to use PowerPivot for your own needs.

Please let me know if you have some ways of using PowerPivot for your own data and reporting needs.

Leave a Reply