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.

Answer 1

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.