VLOOKUP in Pivot Table

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

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 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.

You can download this Pivot Table and Vlookup Excel Template here – Pivot Table and Vlookup Excel Template

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 Tutorial in Excel | Examples
• Pivot Table From Multiple Sheets Examples
• Slicer in Pivot Table
• Filter in Pivot Table
• Sorting in Pivot Table