Value lookup on dates; problem on weekends and PH

[iporing]

Hi

I have a index and match formula set up to lookup for 2 values, yesterday and the day before yesterday. Match function's value lookup on "today-1" and "today-2" which works pretty well except that weekends and ph is no in this data table so if today is 28 dec, my formula will lookup for 27 and 26 dec which is N/A.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Date[/TD]
[TD="class: xl63, width: 64"]Value[/TD]
[/TR]
[TR]
[TD="class: xl64"]31-Dec-15[/TD]
[TD="class: xl65, width: 64"]17,590.66[/TD]
[/TR]
[TR]
[TD="class: xl64"]30-Dec-15[/TD]
[TD="class: xl65, width: 64"]17,711.94[/TD]
[/TR]
[TR]
[TD="class: xl64"]29-Dec-15[/TD]
[TD="class: xl65, width: 64"]17,547.37[/TD]
[/TR]
[TR]
[TD="class: xl64"]28-Dec-15[/TD]
[TD="class: xl65, width: 64"]17,535.66[/TD]
[/TR]
[TR]
[TD="class: xl64"]24-Dec-15[/TD]
[TD="class: xl65, width: 64"]17,593.26[/TD]
[/TR]
[TR]
[TD="class: xl64"]23-Dec-15[/TD]
[TD="class: xl65, width: 64"]17,427.63[/TD]
[/TR]
</tbody>[/TABLE]


Please advice me on how to get the data for 28 dec, to read 24 and 23 dec.

Thanks

Jef

 

[Peter_SSs]

Welcome to the MrExcel board!

Have a look in the Excel Help for the WORKDAY() function & see if that helps you.