GETPIVOTDATA - Help!

sarahrosenberg

Board Regular
I am trying to use GETPIVOTDATA on a pivot table with a lot of blank cells. The top of the pivot table is a "month/year" field. What I am trying to do is look up prices as of a certain date. In some instances the price data for that date is blank and I need my lookup formula to look up the previous price. For example, if the prices in my pivot are as follows:

01/06: \$100
02/06: <blank>
03/06: <blank>
04/06: \$85
05/06: \$85
06/06: \$78
07/06: <blank>
08/06: \$75

I need my formula to pull \$100 for 02/06 and 03/06 and \$78 for 06/06. I pulled all the data - filling in the blanks - into a new table, but I have two row filters in addition to the data column filter and don't know that it is possible to do a vlookup / match with three filters?

Help!

Sarah

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
If your pivot table is in rows 1-3, the dates to lookup are in column A, and the lookups begin with January in cell B5, then cell B6 could be the following:

=IF(GETPIVOTDATA(\$A\$1, A6)=0,B5,GETPIVOTDATA(\$A\$1, A6))

So, if the current lookup is \$0, then the formula returns the previous lookup value.

That is how I populated my table, but now I have a huge data set and I need to pull the data in. I can't figure out how to pull it in from the pivot table or the table I built.

It sounds like your question is more of a problem of filtering more than 3 conditions, rather than GETPIVOTDATA?

Usually SUMPRODUCT is good for these types of situations. It's like using SUMIF for multiple criteria. This site has some useful tips on how to do that:
http://www.excel-vba.com/e-formula-sumproduct.htm

Replies
1
Views
215
Replies
4
Views
90
Replies
8
Views
130
Replies
3
Views
277
Replies
3
Views
96

1,219,905
Messages
6,150,907
Members
450,991
Latest member
ExcelDoer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back