Excel Pivot - Extracting data with 2 column fields and multiple rows

Question

Hi,

I have a pivot like below with 2 fields under columns and then under rows I have different regions and salespeople with their individual figures for each month by books and cd`s (This is just an example but the same layout of the pivot I usually use for reporting).

I have tables in another tab that extracts data from the pivot - A table for each salesperson for their monthly book targets and monthly book sales split. Is there a way where I can easily use a formula to extract the data according to the salesperson, month, and whether its book or cd sales please?

Ive tried using GETPIVOTDATA but there are more regions and salespeople and so its a little long doing this for every salesperson for month, book and cd sales. Ive also tried VLOOKUP but this needs be updated when a new month is entered as the columns expand.

Is there any way to vlookup via the salesperson (row value) and then for a particular month based on book and cd`s?

Thank you.

Answer 1

Use another pivot table and filter to the specific sales person of interest.

If you do the VLOOKUP, use a single cell reference that updates all the column indices, like

=VLOOKUP(Name,PTRange,$Z$1,False)

=VLOOKUP(Name,PTRange,$Z$1+1,False)

=VLOOKUP(Name,PTRange,$Z$1+2,False)

....

=VLOOKUP(Name,PTRange,$Z$1+10,False)