Excel Getpivotdata Vlookup Example
Excel Vlookup from Another Sheet (Table of Contents). Vlookup from Another Sheet in Excel VLOOKUP is an excel function used by excel users who usually need to work with more than one worksheet. It has the ability to extract your data from another worksheet in a very flexible and organized way. In simple terms, this function takes the input from the user, and searches for it in the excel worksheet and returns a matching value related to the same input.
=GETPIVOTDATA(return me this value from the Values Area, any cell within the Pivot Table, [and return me the value that pertains to this Field name, and this Field item],) The GETPIVOTDATA function in Excel returns data stored in a Pivot Table. So essentially it extracts the Pivot Table data. Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data. Returns the grand total of the Sales field, $49,325.
Speaking in a technical way, the generic definition of VLOOKUP function is, that it looks up for a value in the first column of the specified range, and returns the similar value in the same row from another column. The VLOOKUP function is available in all version of Excel right from Excel 2007 to Excel 2016. The VLOOKUP function consists of 4 parameters- lookupvalue, tablearray, colindexnum, and rangelookup. Let us see in detail what do these 4 parameters define in the above syntax. Lookupvalue – Here the user needs to define the value which needs to be searched. It can be either a value which consists of number, date, or text or else it can be a cell reference. Tablearray – Here the user will define the range of cells within which they need to search the data.
Also, values searched are case sensitive i.e. It can search for values in form of text which can be upper or lower case and can find them out. Colindex – Here the value entered refers to the column within the table array in which the users need to find the value. Rangelookup – This parameter is through optional, but is an important parameter to be considered, as it returns approximate match if the range lookup value is TRUE, and returns exact matches if the range lookup is FALSE. Example of Vlookup from Another Sheet in Excel Let us consider an example as shown in the below examples, where we have taken 2 worksheets wherein sheet1 there are names of players with its jersey numbers, but with missing runs scored columns. This column needs to be extracted in sheet1 from sheet2. Vlookup from Another Sheet – Example # 1 Vlookup from Another Sheet – Example # 2 As we can see in example 2 above, additional columns are there such as Column C and D, when compared with sheet2.
We need to retrieve data of Column C from sheet2 to sheet 1. The steps to perform VLOOKUP function are as follows. First enter the formula =VLOOKUP (A2, Sheet2!$A$2:$D$7,3, FALSE) in cell C2, and press enter. We can either type the formula in every cell, but copy pasting will be a much easier and efficient way. Let us see the steps below which will explain the above steps and formula in detail. Step – 1 As we can see in above Step1, the formula entered in sheet1 in cell C2 is =VLOOKUP (A2, Sheet2!$A$2:$C$7,3, FALSE) where,. Little homies figures for sale.
A2 represents the lookup value which is jersey number and it’s the same value in both the sheets. Is the name of the sheet. $A$2:$C$7 is the range of data in sheet2 where we are searching data for the jersey number so as to fetch the data for runs scored in the sheet2. And also the’$’ sign is used to fix the data range so it makes easier to copy and paste in other cells. 3 is the column index-number which contains data in the 3 rd column in the sheet2 and will retrieve data from the range A2: C7.
FALSE will return the exact match if the lookup is in range. Step – 2 Click on cell C2 and drag down the cell from the corner to apply the formula in all the below cells as shown in the below image. As seen in the above image, which is a snapshot of sheet1 where the column C ‘Runs Scored’ is filled using the VLOOKUP formula. VLOOK function can also be used in the same worksheet, as shown in below image.
Step – 3 As we can see in Step 3, we have entered the formula in cell E2, to lookup value for in cell C4. Here in this formula, we have not mentioned the name of the sheet, as we are searching for value in the same sheet. A4 is the lookup value; A2: C7 is the search range i.e. Tablearray and 3 is returned matching value from column C. Step – 4 We can see the result returned in the below image.
Advantages of Vlookup from Another Sheet. This function helps the user to search for data in the same sheet or even in cases when there are multiple sheets present in the workbook. VLOOKUP function is much useful than the MATCH function, as MATCH function does not return the value of data, it only returns the position of that value.
VLOOKUP helps the user to even extract data from one worksheet to another worksheet. Also, the most important feature offered by is that values can be looked up from different workbooks as well. The main advantage of VLOOKUP function is when the user types text in order to be searched, it does not check whether the text is lower or upper case. As the function is not case sensitive.
Disadvantages of Vlookup from Another Sheet. It is difficult for a new user to understand how the formula is implemented, and to know about the parameters involved in it. In case if the user is performing an approximate match, then the first column of tablearray needs to be sorted in ascending order. If not it will return the correct value.
Hence, this function needs to be executed with proper knowledge of the formula. Things to Remember About Excel Vlookup from Another Sheet. The VLOOKUP function never searches for the data in the left. As it always takes up value for the lookup value in the leftmost top column and starts the search from left to right. Always remember, when we enter a value less than 1 in the colindexnum parameter there will be error returned by the formula. For efficient way, use the symbol ‘$’ (absolute cell references) for the tablearray parameter so that the user can copy and paste the formula in other cells without any error. Also, there is no need to type the entire formula, as when it comes to the part of entering sheet name, you can switch the sheet and select the range using the mouse.
You can download this Vlookup from Another Sheet Excel Template here – Recommended Articles This has been a guide to Vlookup from Another Sheet in Excel. The easiest way to adjust a column is to use the Vlookup from Another Sheet in Excel. Here we discuss how to use Vlookup from Another Sheet in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –.
Syntax of GETPIVOTDATA Formula
Example of GETPIVOTDATA Formula
Making GETPIVOTDATA Formula Dynamic
GETPIVOTDATA formula is used with a pivot table to fetch values from it.
GETPIVOTDATA Formula Syntax
The GETPIVOTDATA Formula has the following syntax:
=GETPIVOTDATA (DataField, PivotTableCell, FieldName 1, Item 1, FieldName 2, Item 2, … FieldName N, Item N)
However before we delve any deeper in the GETPIVOTDATA formula, let’s get back to basics for a minute.
Basic structure of a pivot table
A pivot table is based on a collection of data organized as columns (and rows). Each of these columns needs to have a label which acts as the identifier for the entire column. Each of these identifies can be moved around and placed in four “slots”. There are four of these slots in a pivot table:
Each Field in the pivot table would have one or more items. For example in this case, the field “Region” has the names of states (such as Alabama, Alaska, Arizona, Idaho, Illonois etc) Each row of the pivot table now shows the value of “Actual Sales” that took place in a particular region.
Keeping this structure of the pivot table in mind, lets dive straight into getting to know the GETPIVOTDATA formula a little better. A we saw previously, the syntax of the GETPIVOTDATA formula is :
=GETPIVOTDATA (DataField, PivotTableCell, FieldName 1, Item 1, FieldName 2, Item 2, … FieldName N, Item N)
where:
DataField = Name of the Field present in the data slot of the pivot table whose value we want to retreive (Ex. Sum of Sales etc.)
PivotTableCell = Address of any cell located inside the pivot table. This helps the application know which pivot table to use while returning the values.
FieldName = Name of a particular Field present in the Pivot Table
Item = Name of a particular Item present in the Pivot Table
Please keep in mind that any string values must be enclosed in quotes (Ex. “Month”, “Location” etc). Cell references should be used without quotes.
Example of GETPIVOTDATA formula
The easiest way to write a GETPIVOTDATA formula is to simply select a cell and then after pressing the = sign, select a cell located within the pivot table whose value you want to read as shown below.
In the above example the GETPIVOTDATA formula works out be something like this:
=GETPIVOTDATA(“ActualSales”,$B$12,”Month”,DATE(2008,8,31),”Region”,”Alabama”)
Now if you study this formula closely, you would see that the first parameter is “ActualSales”. Why because that in the name of the field from which we would like to extract the data. The second parameter is the value of any cell in the pivot table. Typically excel will make it the very first cell of the pivot table but there is no restriction and you can change this to your liking till the time the cell resides inside the pivot table. After the first and the second parameter, the remaining parameters are just pairs of field name and items – in our case the first pair is “Month” & date (31-Aug-08) while the second pair is “Region” with item as “Alabama”.
Essentially the GETPIVOTDATA formula above can be read as:
Fetch value of “ACTUAL SALES” where “Month” = 31-Aug-08 AND “Region” = “Alabama”.
Important Point:
Keep in mind that GETPIVOTDATA will return only those values which can be located on screen (in one particular cell) – which means that if a particular combination is possible but is not available in the present structure of the pivot table and hence is not summarized in a single cell, it will return an error. In other words, although a combination may be viable, if the entire combination cannot be identified as a single individual cell (although you may be able to get the value by summing multiple cells), GETPIVOTDATA will not give correct results. Hence for GETPIVOTDATA to work correctly, you may need to rearrange the table.
Let’s take an example. Suppose you wanted to get the total value of sale for the product category “Electronics” for the month of Aug. In the first table, we can find the value but indirectly – we will have to sum of the individual values of the product across all regions. Which also means that if we wanted to locate a single cell that has the value we are looking for, we would fail. Hence the GETPIVOTDATA formula shown below would fail.
Now let’s rearrange the pivot table a bit. Let’s move the product to the first position in the row fields and the regions to the second. Now we do have a single cell which can give us the value we are looking for. Hence the GETPIVOTDATA function in the second case returns a proper value without any error.
Modifying the GETPIVOTDATA formula to make it even more dynamic
By default, the GETPIVOTDATA formula will update whenever the pivot table gets updated with fresh data points. However due to its structure, the GETPIVOTDATA formula possesses can be made much more versatile. If you look at the structure of the GETPIVOTDATA formula, it has a number of (Field Name, Item) keys. Now both these parameters can be changed on the fly but let’s focus on the second param – Item. Let’s see how we can make a single GETPIVOTDATA formula return different values based on different criteria. For example let’s replace “Alabama” with a cell reference say, B2 in the following formula:
GETPIVOTDATA(“Sum of ActualProfit”,$A$5, “Region”, “Alabama”, “) changed to
GETPIVOTDATA(“Sum of ActualProfit”,$A$5, “Region”, B2)
As we can see, the same formula returns different values based on what the use choose in the drop-down.
Now whenever the value in cell B2 changes, the GETPIVOTDATA formula will return a new value. Till the time we can ensure that B2 will always provide a valid value for a Region (from the list of regions that we have in our pivot table), the formula will work without a hitch.