The pivot table is one of Excel’s strongest features. A pivot table is a statistical table that may summarize and reorder data from an extensive/broad table. This tool aids in the shortening of data, categorizing data, and creating one’s customized group. VLOOKUP, on the other hand, is a function in Excel that is used to find things/values in a data or range by row. This article will demonstrate how to use VLookup within the Pivot Table.
How to Use VLookUp and Pivot Tables?
The VLOOKUP tool obtains information from an Excel table based on specific search parameters. For example, suppose the table has two columns: “Product name” and “Price.” Then, another table nearby will conduct searches in the first table and use the criteria “name of the product” and return the value of the related pricing.
- We proceed to the cell in the second table with the column name “Price.”
- Select “FORMULAS” – “Lookup and Reference” – “VLOOKUP” from the drop-down menu.
- You may also use the “Insert Function” button to type =VLOOKUP(). To do that, click on the “fx” button at the top of the formula bar. Alternatively, you can use the hotkey combination SHIFT+F3.
- Select “Lookup and Reference” from the drop-down list in the displayed dialogue box on the category field and specify the function below.
- We fill up the function’s arguments.
We enter a reference to the cell beneath the product name of the second table D3 in the “Lookup value” column. Enter the span of all values from the first table A3:B7 in the “Table array” box. Finally, we enter “2” in the “Col index num” field because the price we would like to get when browsing for the goods is in the second column. And then click OK.
Now, under the second table’s column title “Name product,” insert the names of the commodities for which we need a price. The function enables us to swiftly discover the data and obtain all required information from huge tables. It’s similar to working with databases. When an inquiry is submitted to the database, the results, which are a response to the query criteria, are output.
How to Use VLookUp with Two Pivot Tables:
Let’s make the task a little more complicated by modifying the structure and increasing the quantity of data in the table. Add columns to the first table to increase the amount of data: “January,” “February,” and “March.” In the first quarter, we shall record the sales figures there.
Now we must make a different selection of data for the product using the VLOOKUP function and total the sales for the first quarter. To do so, proceed to cell H3 and, after calling the function, fill in the following arguments:
- A11 is the lookup value.
- A3: H7 table array Our table’s range has been expanded.
- 4,6,8 is the column index number. Because we need to use the function to address multiple columns simultaneously, the value of this argument will be taken into the array by curly braces. In addition, column numbers should be separated by a semicolon.
- FALSE for range lookup.
- The complete function must then be inserted within the SUM () function to sum the numbers in the selected columns.
- After inserting this formula, hit the CTRL+SHIFT+ENTER key combination. Note that the formula will function incorrectly if you do not hit a combination of these keys. In Excel, you may need to perform various functions in an array. When entering a formula, you must use CTRL+SHIFT+ENTER. Then, on the formula line, all of the material will be enclosed in curly brackets,””, indicating that the formula will be executed in the array.
- Now, in cell A11, input the product’s name, and in cell B11, we get the sales amount for this product in the first quarter.
The VLOOKUP function in Excel compares two tables, and after the matching of the necessary data is established, their values are instantly substituted for summing by the SUM function. The entire procedure is carried out cyclically thanks to an array of functions, as represented by the curly brackets in the formula bar.
The main drawback of the VLOOKUP function is the inability to choose multiple identical beginning values in the query. In other words, if the values “pear” and “apple” are repeated in our table, we cannot total up all the pears and apples.
VLOOKUP and Pivot Tables are two features that work well together. Tables are typically delivered to you by several methods and individuals. As a result, VLOOKUP can aid in preparing data for Pivot Tables, which can then be used to perform analysis and generate reports.