PivotTable

3 posts

Harness the (Power) of Excel VLOOKUP

Excel VLOOKUP is quite possibly one of the most popular and yet misunderstood Excel functions. It is a powerful function that can be used to simplify and automate so many things in Excel.

As an Excel user advances in their skills, VLOOKUP typically becomes one of the next powerful tools used.

See how to use it, as any tool becomes more useful as the user’s knowledge and skill in manipulating it increases.

Start With the Basics

Basically, what VLOOKUP does is look in a column of a table of data for a value that you tell it to find. It then returns whatever value is in the same row of that table of data, but from a column that you tell it to look in.

The one restriction with VLOOKUP is that you can’t return a value that is to the left of the column that you are looking in. You can only return values in columns to the right.

Examine the Excel VLOOKUP Function

Here’s the function:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

  • lookup_value = the value you want to find in the table
  • table_array = the table of data that you want to look in
  • col_index_num = the column in the table of data that you want the value returned to you
  • range_lookup = logical value. If you want to find the closest match (TRUE) or an exact match (FALSE)

See How Excel VLOOKUP Is Used

The image below is a table that will be used for the VLOOKUP function. The store number of ‘1184’ will be looked up in the table to find that store’s phone number. It’s in cell A4 and highlighted in a red box.

vlookup-table-highlighted-value
Table of Data for VLOOUP

This is the formula that will be used to get the phone number for that store number using VLOOKUP:

=VLOOKUP(1184,A2:C5,2,FALSE)

The result of that formula will be (555) 555 6492, which is the value in cell B4.

Let’s break the formula down into plain English:

“Using the data in cells A2 through C5, find the exact match of the value ‘1184’ in left-most column. Once you find ‘1184’ in that column (which is always column 1), show the value in column 2 of that same row.”

The Real Power of VLOOKUP

In the example above, the value of “1184” was hard-entered into the formula. That was just done for the sample.

The real power of VLOOKUP is that you can pass a cell value, a formula result, or the result of a function to VLOOKUP.

Imagine that you have a table of data with those store numbers. It has hundreds, thousands, or even more rows. You want to know the address and phone number for each of those rows.

Just like this sample that has only a few rows:

data-needing-excel-vlookup
Sample Data Needing VLOOKUPs

All that you need to do is add two columns to the data – one for the address, and one for the phone number.

Now, add the VLOOKUP formula to those two columns, and instead of hard-entering the value that you want to look up, enter the cell address that has the store number.

This is what it will look like:

excel-vlookup-formula
The VLOOKUP Formulas

Copy that formula down in each column, using this technique, and you now have all of that information available.

Note, as seen in the formulas above, that you will need absolute references to the cells that are the data table if you want to use AutoFill to copy the formulas down. This ensures that each formula looks in the same, correct range of cells.

Here is the result:

excel-vlookup-result
Result of Adding VLOOKUPs

Conclusion

VLOOKUP is a great time saver that allows you to lookup values in a cell in a table of data and pull another value from the same row that it found the data in. The resulting data can also be used in a PivotTable. The possibilities are nearly endless.

With the PowerPivot feature being added to Excel you have an option to VLOOKUPs. See this post to read more about PowerPivot and how to use it instead of VLOOKUP.

Let me know if you have any tips or questions related to using the VLOOKUP function.

How to Enable PowerPivot for Excel

PowerPivot is a great tool for handling and reporting on data from a variety of sources. To use it, there a few steps that need to be taken first.

Depending on which version of Excel that you have, the process is different.

For Excel 2010, you first must download Microsoft PowerPivot from here and then install it.  There are two versions available. You will need either the 32-bit or 64 bit version that matches the version of Excel that you have. Once you have downloaded and installed it, you’re done. The PowerPivot tab in Excel 2010 is automatically added. When you open up Excel you should be able to see the tab.

For Excel 2013 and Excel 2016, PowerPivot (changed to Power Pivot, notice the space now) is included with Excel, and just needs to be enabled.

To enable PowerPivot (or Power Pivot) for Excel 2013 and Excel 2016, just follow these steps:

Step 1:

First, click the File tab, then select Options.
powerpivot

Step 2: 

Under Options, click Add-ins, and then choose COM Add-ins under Manage. Then click GO…

powerpivot

Step 3:

Lastly, check the box beside Microsoft Power Pivot for Excel, and then click OK.

powerpivot

The Excel Power Pivot tab will now be visible. Now you can go and do some really amazing things in Excel.

powerpivot

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.