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.
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)
How to use VLookup in Pivot Table Excel?
Lets follow the below steps.
- 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.
If we want to get the category against each product. So here we are using the VLookup to get the data from another worksheet. - The formula you look into the screenshot below searches for the text “Apple” in column B in the “Data” worksheet.
- Enter the formula.
In the formula, it is lookup for the value product “Apple” which is in cell B5 from another worksheet.
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. - Clicks enter to get the result.
- Drag the same formula against each product category.
You will get the category against each of the products by using the formula VLookup.
Things to Remember
- While creating a pivot table, make sure there will be no blank column or row.
- For the pivot table, Data should be in the right and correct form.
- Refresh the pivot table manually.
- In the pivot table, always add the unique value in your column fields.
- Always create a pivot table in the new worksheet, if you are the beginners or new users.
- Try to summarize the data as much as possible to get better observation
- VLookup always searches for the value in the leftmost column of the lookup range.
- VLookup is a case insensitive in nature.
- VLookup can summarize or categorize the data in a very easy form.
- 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 –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
46.8K subscribers
Leave a Reply