Excel Functions Tutorials
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- HLOOKUP Formula in Excel
- HLOOKUP Examples
- Hyperlink Excel Function
- Hyperlink Formula in Excel
- INDIRECT Function in Excel
- LOOKUP Excel Function
- LOOKUP Formula in Excel
- Match Excel Function
- MATCH Formula in Excel
- How to Match Data in Excel?
- VLOOKUP Excel Function
- VLOOKUP Formula
- VLOOKUP Tutorial in Excel
- VLookup in VBA Excel
- VLOOKUP in Pivot Table
- VLOOKUP with SUM
- VLOOKUP with Match
- SUMIF With VLOOKUP
- VLookup with IF Statement
- Vlookup to the Left
- VLOOKUP from Another Sheet / Workbook
- VLOOKUP Examples in Excel
- VLOOKUP Table Array
- VLOOKUP vs HLOOKUP
- INDEX Excel Function
- INDEX Formula in Excel
- Indirect Formula in Excel
- TRANSPOSE Excel Function
- Row Function in Excel
- Rows Function in Excel
- OFFSET Excel Function
- OFFSET Formula in Excel
- VLOOKUP with Multiple Criteria
- IFERROR with VLOOKUP in Excel
- Fixing VLOOKUP Errors
- Alternatives to Vlookup
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
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.
Using VLOOKUP in Excel Pivot Table
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 help 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.
How to use VLookup in Pivot Table Excel?
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.
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.
Step 3 – 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 value we are looking for a product is in column 2 and 0 means to find the exact match against each row.
Step 4 – Clicks enter to get the result.
Step 5 – Drag the same formula against each product category.
You will get the category against each of the product 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 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.
You can download this Pivot Table and Vlookup Excel Template here – Pivot Table and Vlookup Excel Template
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 –