How to do a range vlookup using the output of a pivot table column in excel?

2
votes

These are the monthly salaries of the employees (obtained using the pivot table):

enter image description here

Each cell will then be used as the Lookup value for the vlookup or index and match functions.

The lookup value is to be looked up in column A and column B of the table below and if it is matched (within the range), it will return the corresponding value under column C.

enter image description here

I have tried:

1.) Placing this sample formula outside the pivot table:

=VLOOKUP(GETPIVOTDATA("Sum of Reg Pay",$A4,"Person","JOHN"),SSSContribution[#All],3,TRUE)

But the problems with the 1st solution are:

a.) The person's name is fixed as string which makes unable to adjust to the drag down.

b.) It is not a calculated field, so every adjustments to the pivot table will require adjustment of the cells containing the formula.

2.) Putting this formula in the Calculated fields feature of the pivot table:

= vlookup( 'Reg Hrs.','SSS Contribution Table'!A$2:D$32,3,TRUE), but I get the error below:

enter image description here

So how to do a range lookup for the output values of a column in a pivot table?

1
excelpivot-tablevlookup
What version of Excel do you have? – jeffreyweir
2007 at the office. 2016 at home but I can upgrade if there are missing features that is needed in more recent versions – Pherdindy
Okay. If you had Excel 2013 or later you could also use something called the DataModel to do this, all inside the PivotTable using something called DAX formulas. – jeffreyweir

1 Answers

2
votes

Replace the entire GETPIVOTDATA function and arguments with the cell reference to the cell concerned, and then copy down. e.g. =VLOOKUP(E2,SSSContribution[#All],3,TRUE)

Note that you can turn off the automatic generation of the GETPIVOTDATA syntax by unchecking this option:

enter image description here

To keep your formula 'synced' to your PivotTable, you'd need to use code something along the lines of this answer of mine:

https://stackoverflow.com/a/38867978/2507160