see the attached
there is a table calculation - lookup - that says in the table retrieve the value that is a number of places (cells in excel) back from the current row
it will return this in your workbook
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
I actually forgot to mention that i have a dynamic data set where the date in the column range is not fixed. Above method is works fine with fixed range.
Thanks for the help.