< Go Back

Using VLOOKUP to retrieve information from different Excel files

After generating a Pivot Table, you can retrieve specific data from the Pivot Table using the VLOOKUP and GETPIVOTDATA functions. When you use any of these functions, you are basically trying to find your lookup-value on another location in Pivot Table and retrieve specific information related to that value. The functionality of both functions is more or less the same but their syntax is different.
VLOOKUP function on Pivot Table

Excel VLOOKUP function can be used to retrieve information from Pivot Table based on cell references. As VLOOKUP function is designed to retrieve data in a table organized into vertical rows, so the lookup value must present in the most-left column and targeted value to be retrieved can be in any column to the right, which is called “ column index”.

Syntax

=VLOOKUP (lookup_value, table, col_index, [range_lookup])

In this article, suppose we have a dataset of sales records of some famous cigarette brands in five different regions, and we have summarized the sales of each brand in each region in a Pivot Table as shown below.

Now, suppose we want to retrieve the summarized sales figure of a specific brand (Lookup value) for a specified region (col_index) from this Pivot Table using VLOOKUP function as per above syntax.

Here we need to provide the cell references of lookup-value, pivot table, col_index, and range_lookup to get the resulting information.

=VLOOKUP (H17, B3:H14, 6, FALSE)

As you can see here, we have used following cell reference in this formula

Lookup-value– Cell H17 (NEWPORT) value to look for in the first column of the pivot table

Table – Pivot Table cells range reference B3:H14 as a table from which to retrieve a value.

col_index – WEST region is 6th column in the table from which to retrieve a value.

range_lookup – [optional]

  • TRUE = approximate match (default).

  • FALSE = exact match.

If you are using VLOOKUP function in another worksheet to retrieve the data from Pivot Table sheet, then this formula will incorporate the Pivot Table sheet name as a reference to the table, like;

=VLOOKUP (H17, Pivot_Table!B3:H14, 6, FALSE)

If you want to pick the col_index number automatically with respect to region cell reference, then we can achieve this by introducing MATCH function within VLOOKUP function, like;

=VLOOKUP (H17,B3:H14,MATCH(H18,B4:H4,0),FALSE)

Now the MATCH function will look up for region cell reference value, as given in cell H18, in column header range of Pivot Table, B8:H4, and will return the column index number where there would be a match. Like, in above example, region “West” is matched in cell G4, so it will return the column index number as 6, because column G is 6th column in Pivot Table range.

GETPIVOTDATA function on Pivot Table

The Excel GETPIVOTDATA function retrieves data from a Pivot Table based on Pivot Table structure, instead of cell references. It is more flexible and dynamic function to get results. You don’t need to remember the syntax and it is generated automatically when you type an equal sign “=”anywhere in worksheet or workbook and click on a value cell as the reference in Pivot Table like you enter this in cell H19

= click on cell F8 in Pivot Table and press ENTER

It will automatically generate GETPIVOTDATA function with respect to value cell F8, like this:

=GETPIVOTDATA("Sales",$B$3,"Region","East","Brand","NEWPORT")

However, you can turn off the Generate GetPivotData in the menu at Pivot Table Tools > Options and can create GETPIVOTDATA formula by following its syntax as per your own requirement.

Syntax

=GETPIVOTDATA (data_field, pivot_table, [field1, item1], [field2, item2], ..... )

Where,

data_field – The name of the value field to query.

pivot_table – A reference to the cell where pivot table starts in.

field1, item1 – [optional] – A field/item pair.

These optional field/item pairs are used to limit the data retrieval like applying filters based on pivot table structure. You can limit your result by introducing multiple field/item pairs as per pivot table structure.

In short, in GetPivotData formula you refer to the pivot table, and the field(s) and item(s) that you want the data for. In this example, you refer to the pivot table cell $B$3, and want sales data ( data_field) from two field/item pairs, like Region/West and Brand/Camel.

In the formula, data_field must be provided in double quotes, and names of field/item pairs can be in double quotes or you can provide cell references, like followings;

=GETPIVOTDATA("Sales",$B$3,”Region”,”East”,”Brand”,”Camel”)
OR
=GETPIVOTDATA("Sales",$B$3,G16,H16,G17,H17)

You can also provide data_field as a cell reference, but you need to concatenate it with double quotes in start or end of the cell reference, otherwise, you will get an error in the formula, like this;

=GETPIVOTDATA(G18&"",$B$3,G16,H16,G17,H17)

VLOOKUP vs. GETPIVOTDATA functions

Here are some points which differentiate both functions from each other.

  • VLOOKUP function is not as flexible as GETPIVOTDATA function.
  • VLOOKUP function is dependent to table range reference provided in the formula. It is not effective if the Pivot table range expands. While GETPIVOTDATA is based on pivot table structure, so it remains effective even if the pivot table expands or collapses.
  • GETPIVOTDATA function is dynamic and you can extract data from multiple pivot tables which follow same pivot table structure using a single formula. While VLOOKUP function is not dynamic.
  • In GETPIVOTDATA function you can filter your results by introducing multiple field/item pairs, but it is not possible with VLOOKUP function.

If you haven’t found your answer in this article, our Excel experts are available to help you save hours of researching information. Use this link to type your question and connect to a qualified Excel expert in a few seconds and they will solve your problem on the spot in a live, 1:1 chat session.

 
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar