How to select data based on previous date in a excel table?

Question

I have a MS Excel table named 'equity' with the below columns.

Date        Open    Close   PrevOpen    PrevClose
18-Jul-18   99.8    101.9
19-Jul-18   100.7   102.1
20-Jul-18   101.2   103.7
23-Jul-18   102.1   99.8
24-Jul-18   101     102.8

table-1

For each row I need to get the previous date's open price and close price. Regardless of the order of the rows.

For example, my second row should be as given below.

Date        Open    Close   PrevOpen    PrevClose
18-Jul-18   99.8    101.9       
19-Jul-18   100.7   102.1   99.8        101.9
20-Jul-18   101.2   103.7       
23-Jul-18   102.1   99.8        
24-Jul-18   101     102.8

ExpectedResult

Thanks for any help.

Are there any dates skipped in the list? And if so, what would the result be for e.g. 23-Jul-18? — ffonz, Jul 24 '18 at 13:39
@ffonz Yes there are skipped dates. The price list is available only for weekdays as Saturday and Sunday are market holidays. For July23, the data should be from July20. — Roy, Jul 24 '18 at 13:54

score 0 · Answer 1 · answered Jul 24 '18 at 13:42

Here is the solution

First select the cells from A1 to the last cell of column "close" and then press ctrl+alt+f3. the box appears give the name "Data"
In Prev open col add this formula and drag the formula till the last cell of table in same column

=IFERROR(VLOOKUP(A2-1,Data,2,FALSE),"-")
In Prev Close col add this formula and same drag to the last row

=IFERROR(VLOOKUP(A2-1,Data,3,FALSE),"-")

score 0 · Accepted Answer · answered Jul 24 '18 at 13:53

In Column PrevOpen, use:

=IFERROR(INDEX([Open],MATCH(MAX(INDEX(([Date]<[@Date])*[Date],0)),[Date],0)),"")

In Column PrevClose, use:

=IFERROR(INDEX([Close],MATCH(MAX(INDEX(([Date]<[@Date])*[Date],0)),[Date],0)),"")

These will return the Open / Close values from the immediately previous date, ignoring any date gaps, and ignoring table sort order.

How to select data based on previous date in a excel table?

2 Answers2