WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » VLOOKUP in Pivot Table

VLOOKUP in Pivot Table

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Combine Vlookup and Pivot Table in Excel

To use VLOOKUP in pivot table is similar to using VLOOKUP function to any other data range or table, select the reference cell as the lookup value and for the arguments for table array select the data in the pivot table and then identify the column number which has the output and depending on the exact or close match give the command and execute.

The pivot table is one of the most powerful functions of excel. A pivot table is a table of statistics that help to summarize and reorganize the data of a wide/broad table. This tool helps to shorten the data and help to analyze the data categorize wise and create own customized group. On the other hand, VLOOKUP is a function which used in excel when you are required to find things/value in a data or range by row. In this article, we look at how to use VLookup within the Pivot Table.

VLOOKUP in Pivot Table

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
  • Step 1 – Select the data on a worksheet for which you want to match the value from another worksheet. Deselect the category from the pivot table, we will use the VLookup here to get the category of Product.

VLOOKUP in Pivot Table Example 4.1

If we want to get the category against each product. So here we are using the VLookup to get the data from another worksheet.

  • Step 2 – The formula you look into the screenshot below searches for the text “Apple” in column B in the “Data” worksheet.

VLOOKUP in Pivot Table Example 4.2

  • Step 3 – Enter the formula.

Example 4.3

In the formula, it is lookup for the value product “Apple” which is in cell B5 from another worksheet.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Pivot Table’!C2:D42: It means we are getting the lookup value from sheet Pivot Table against apple.

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

  • Step 4 – Clicks enter to get the result.

Example 4.4

  • Step 5 – Drag the same formula against each product category.

Example 4.5

You will get the category against each of the products by using the formula VLookup.

Things to Remember

  1. While creating a pivot table, make sure there will be no blank column or row.
  2. For the pivot table, Data should be in the right and correct form.
  3. Refresh the pivot table manually.
  4. In the pivot table, always add the unique value in your column fields.
  5. Always create a pivot table in the new worksheet, if you are the beginners or new users.
  6. Try to 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 a case insensitive in nature.
  9. VLookup can summarize or categorize the data in a very easy form.
  10. Once you used the VLookup formula, don’t shift the column or row it leads to a mismatch of your VLookup value.

Recommended Articles

This has been a guide to VLOOKUP in Pivot Table Excel. Here we discuss how to use VLOOKUP Functionality in a Pivot Table along with examples and downloadable excel template. You may learn more about excel from the following articles –

  • VLookup with Two Criteria
  • VLookup with Match
  • VLOOKUP Tutorial
  • Slicer in Pivot Table
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Vlookup in Pivot Table Excel Tempalte

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More