Need suggestion on best "lookup" function to use

[RT_Indy]

I am looking for suggestions on the best way to populate some excel reports
based on a pivot table. The pivot table will have 400 – 500 rows of data
(Widget1_NewSale_Midwest), and the columns represent weeks (2008_12, etc…).


As each week passes, the pivot table will get updated with a new weeks
worth of sales data. What I am looking for is a recommendation on the best
function to use in the reports tabs of the workbook to look up data in the
pivot table that requires the least amount of maintenance. SUMIF works good
if the rows are fixed, but if I have a new row of data inserted in the middle
it will return incorrect results. GETPIVOTDATA works, but the formula needs
to be updated every week to pull from the right column – ie replace the week
reference it is to look up.

Any expert suggestions are much appreciated.

 

[Pete_UK]

Both SUMIF and VLOOKUP can use full-column references, so I'm not sure
what problem you have by inserting a new row in your table - perhaps
you can supply some more details?

Pete

 

[RT_Indy]

First of all, thanks for the quick response.

I need to look up both the row and the column in the report and return the
corresponding value from the pivot table, hence my initial stab at
GETPIVOTDATA. I believe SUMIF will work for row or column, but not both.
VLOOKUP will look vertically, but I can't figure out how to manipulate the
Col_Index_num to look up the column as well, if that is possible.
Essentially, I need to replace the "3" below with something that will tell
Excel what column to look up as well.

=VLOOKUP($B:$B,ReportData!1:10000,3)

Thanks,

 

[Pete_UK]

Have a look at INDEX and MATCH in Excel Help - basically you will have
a formula along the lines of:

=INDEX(table_range,MATCH(for_row),MATCH(for_column))

Sorry to be a bit vague, but you still haven't provided any details of
cells used etc.

Hope this helps.

Pete

 

[RT_Indy]

Pete - I got it to work with the INDEX/MATCH function. I really appreciate
your help.

 

[Pete_UK]

Glad to hear it, Robert - thanks for feeding back.

Pete