VLOOKUP in Pivot Table

Updated on January 2, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Combine Vlookup and Pivot Table in Excel

To use VLOOKUP in a PivotTable is similar to using the VLOOKUP function in any other data range or table:

  1. First, select the reference cell as the lookup value.
  2. Next, choose the data in the PivotTable for the table arguments array and then identify the column number with the output.
  3. Depending on the exact or close match, give the command and execute.

The PivotTable is one of the most powerful functions of Excel. A PivotTable is a table of statistics that helps to summarize and reorganize the data of a wide/broad table. This tool helps shorten the data and helps analyze the data categorize-wise and create a customized group. On the other hand, VLOOKUP is a function used in Excel when you are required to find things/values in data or range by row. This article looks at how to use VLOOKUP within the PivotTable.

VLOOKUP in Pivot Table

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VLOOKUP in Pivot Table (wallstreetmojo.com)

VLOOKUP Excel Function – Explained in Video

 

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to use VLookup in Pivot Table Excel?

You can download this Vlookup in Pivot Table Excel Tempalte here – Vlookup in Pivot Table Excel Tempalte

Let us follow the below steps.

  1. Select the data on a worksheet for which you want to match the value from another worksheet. For example, deselect the category from the PivotTable. We will use the VLOOKUP here to get the category of product.


    pivot and vlookup in excel example 4.1

    Suppose we want to get the category against each product. So, here we are using the VLOOKUP to get the data from another worksheet.

  2. The formula in the screenshot below searches for the text “Apple” in column B in the data worksheet.


    pivot and vlookup in excel example 4.2

  3. Enter the formula.


    pivot and vlookup in excel example 4.3
    The formula looks for the value product “Apple,” in cell B5 from another worksheet.

    Pivot Table’!C2:D42: We are getting the lookup value from sheet PivotTable against apple.

    2,0 indicates that the value we are looking for a product is in column 2, and 0 means to find the exact match against each row.

  4. Press the “Enter” key to get the result.


    pivot and vlookup in excel example 4.4

  5. Drag the same formula against each product category.


    pivot and vlookup in excel example 4.5

    You will get the category against each product by using the formula VLOOKUP.

Things to Remember

  1. While creating a PivotTable, ensure there is no blank column or row.
  2. For the PivotTable, data should be in the right and correct form.
  3. We must Refresh the pivot tableRefresh The Pivot TableTo refresh pivot tables, you may use the following methods - refresh pivot table by changing data source, refresh pivot table using right click option, auto-refresh pivot table using VBA Code, refresh pivot table when you open the workbook.read more manually.
  4. In the PivotTable, always add the unique value in your column fields.
  5. We must always create a PivotTable in the new worksheet if you are a beginner or new user.
  6. We must summarize the data as much as possible to get better observation.
  7. VLOOKUP always searches for the value in the leftmost column of the lookup range.
  8. VLOOKUP is case insensitive.
  9. VLOOKUP can summarize or categorize the data in a very easy form.
  10. Once we use the VLookup formulaVLookup FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more, we must not shift the column or row. It leads to a mismatch of your VLOOKUP value.

Recommended Articles

This article has been a guide to VLOOKUP in PivotTable Excel. Here we discuss using VLOOKUP functionality in a PivotTable along with examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

Reader Interactions

Comments

  1. Steve says

    I have it working, thank you!!

Leave a Reply

Your email address will not be published. Required fields are marked *