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

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

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:

Please Login or Register  to view this content.

This seems to work:

Formula:

Please Login or Register  to view this content.

Adjust for the other columns. For example:

Formula:

Please Login or Register  to view this content.

And:

Formula:

Please Login or Register  to view this content.

This is the updated workbook

Is this resolved?

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