Dynamic formula which compares a cell to a prior range of cells

[nathanwlee]

Excel gurus out there, I need your help! Normally after a bit of googling, I seem to find a solution to formula issues. This one I am struggling...

I am working on sales pipeline data.

I have a vertical data set which continually grows each week as data is input on there from each extract from the system. (ie so at week 10 I have 10 rows of data of the same sales item (opportunity))

I am trying to create a number of formulas which are all dynamic in the sense that they look at the current line of data and returns a comparison to the previous data extract of data (row).

I have attached an example spreadsheet which hopefully demonstrates what I would like the formula to return.
However, there is a number of issues with the formulas as they are:

1) They rely on the data in each extract to be in the same order (which they are not)
2) They are not fixed as I would normally do, with an INDEX MATCH checking that row of data to the previous (usually use opportunity number)

I have attached an example datasheet which hopefully shows this...

So far I believe it may be something to do with OFFSET...

Any help/ suggestions would be greatly appreciated

[protonLeah]

Named Ranges:
Close_Date =DATA!$D$2:INDEX(DATA!$D:$D,COUNTA(DATA!$D:$D))
Data_Extract_Number =DATA!$A$2:INDEX(DATA!$A:$A,COUNTA(DATA!$A:$A))
Opp_Names =DATA!$C$2:INDEX(DATA!$C:$C,COUNTA(DATA!$C:$C))
Opportunity_Name_List =DATA!$C$2:$C$11
Pipeline_Stage =DATA!$F$2:INDEX(DATA!$F:$F,COUNTA(DATA!$F:$F))
Values_data =DATA!$E$2:INDEX(DATA!$E:$E,COUNTA(DATA!$E:$E))

---

Formula for column G:

=IFERROR($F12-INDEX(INDEX(Pipeline_Stage,MATCH($A12-1,Data_Extract_Number,0)):INDEX(Pipeline_Stage,MATCH($A12-1,Data_Extract_Number)),MATCH($C12,OFFSET(Opp_Names,MATCH($A12-1,Data_Extract_Number,0)-1,0,10),0)),"New")

[TMS]

This is the updated workbook

[TMS]

This seems to work:

=--(F12-IFERROR(VLOOKUP($B12,$B2:$F11,5,FALSE),0))

Adjust for the other columns. For example:

=--(D12-IFERROR(VLOOKUP($B12,$B2:$F11,3,FALSE),0))

And:

=--(E12-IFERROR(VLOOKUP($B12,$B2:$F11,4,FALSE),0))

[TMS]

Is this resolved?

[nathanwlee]

Thank you all for your help. It is fixed now.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.