Index (Match) to locate pivot table data, based on column header and matching row item

[Kimberlieh]

My active worksheet called "Estimate", includes my billing items from a formula in cells B13:B39, and a user entered month in cell M8 for the period the statement is being created for.

The Pivot table is on a worksheet called "Pivot Data", and contains the information I need to summarize in the Estimate worksheet.

In the Estimate worksheet I need to populate J13:J39 with the quantity from the column in the Pivot Data worksheet with the same month header as in M8 and that also matches the billing item in B13:B39.

The formatting for both sheets is "custom", with a comma. This is the formula I have in J13,

=INDEX('Pivot Data'!D5:O31,MATCH(Estimate!M8,'Pivot Data'!D5:O31,0),MATCH(B13:B39,'Pivot Data'!B5:B31))*E13 but it returns a #NA .

I tried to use a post from several years ago, but couldn't make it work. I will be grateful for help resolving this conundrum.

Kimberlie

 

[Jerry Sullivan]

Hi Kimberlie,

Have you tried using a formula with the GETPIVOTDATA function?

Using GETPIVOTDATA is typically a better method of extracting data at the intersection of PivotTable items than INDEX-MATCH.

 

[Kimberlieh]

Hi Kimberlie,

Have you tried using a formula with the GETPIVOTDATA function?

Using GETPIVOTDATA is typically a better method of extracting data at the intersection of PivotTable items than INDEX-MATCH.

Jerry,

No, I haven't considered using the GETPIVOTDATA function, and I don't have experience with it. I'm open to learning how it works, especially if it's a better method than the INDEX-MATCH.

Can it return the data from variable columns, and the row with a matching billing item?

 

[Jerry Sullivan]

Yes, it can.

I find the easiest way to make a formula using GETPIVOTDATA is to start by letting Excel build a formula to a single value.

In the top left cell of the range that you want the data returned enter an equal sign, "=" (no quotes), then pick the cell in the PivotTable that represents the value at the intersection of the matching billing items.

Excel will enter a formula like this....
=GETPIVOTDATA("Sales",$A$4,"Month","Aug","Office","South")

Next modify the formula to use cell references instead of constant values for the PivotItems.
If your months are on Row 2 with "Aug" in cell X2, and Offices are in Column W with "South" in cell W3, your modified formula might look like....
=GETPIVOTDATA("Sales",$A$4,"Month",X$2,"Office",$W3)

Now you can copy the formula and paste it down and across your range of results and it will reference each matching cell on the PivotTable.

 

[Kimberlieh]

Yes, it can.

I find the easiest way to make a formula using GETPIVOTDATA is to start by letting Excel build a formula to a single value.

In the top left cell of the range that you want the data returned enter an equal sign, "=" (no quotes), then pick the cell in the PivotTable that represents the value at the intersection of the matching billing items.

Excel will enter a formula like this....
=GETPIVOTDATA("Sales",$A$4,"Month","Aug","Office","South")

Next modify the formula to use cell references instead of constant values for the PivotItems.
If your months are on Row 2 with "Aug" in cell X2, and Offices are in Column W with "South" in cell W3, your modified formula might look like....
=GETPIVOTDATA("Sales",$A$4,"Month",X$2,"Office",$W3)

Now you can copy the formula and paste it down and across your range of results and it will reference each matching cell on the PivotTable.

Jerry,

Thank you, I modified the formula and it works, well almost. Each of the months are a pivot table column. The worksheet has a cell for the user to enter the month for the report. I tried to enter the cell reference in the formula, but it just results in #REF !.

The following is my current formula
=GETPIVOTDATA("Mar",'Pivot Data'!$A$4,"Bid Item Number",'Pivot Data'!$A5,"Description",'Pivot Data'!$B5)*F13

I get the error when I change the "Mar" to $M$4.

How can I get the formula to work with the user entered month?

Kimberlie

 

[Jerry Sullivan]

Kimberlie,

Using a reference for the first parameter (the data_field) is handled a little differently than the other field parameters.

You need to add ""& to cast this argument as a String...
=GETPIVOTDATA(""&$M$4,'Pivot Data'!$A$4....