formula to lookup data in cell NEXT to another cell

[TraderXL]

I have downloaded some historical quotes for MSFT in one spreadsheet. IN
another sheet I want to write a formula to say find June 19, 2009 and look
for the previous TRADING DAY (not previous day, which could be a weekend
day). How do I do that?

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Gary''s Student]

In most cases, we would use a VLOOKUP() on the date in question. In your
case however, we want to lookup using the previous day (excluding Sat or Sun)

In A1, setup a list of consecutive day, in B1 enter:

=A1-(WEEKDAY(A1)=1)-1-2*(WEEKDAY(A1)=2) and copy down. We see:

Saturday 06/20/09 Friday 06/19/09
Sunday 06/21/09 Friday 06/19/09
Monday 06/22/09 Friday 06/19/09
Tuesday 06/23/09 Monday 06/22/09
Wednesday 06/24/09 Tuesday 06/23/09
Thursday 06/25/09 Wednesday 06/24/09
Friday 06/26/09 Thursday 06/25/09
Saturday 06/27/09 Friday 06/26/09
Sunday 06/28/09 Friday 06/26/09
Monday 06/29/09 Friday 06/26/09
Tuesday 06/30/09 Monday 06/29/09
Wednesday 07/01/09 Tuesday 06/30/09
Thursday 07/02/09 Wednesday 07/01/09
Friday 07/03/09 Thursday 07/02/09
Saturday 07/04/09 Friday 07/03/09
Sunday 07/05/09 Friday 07/03/09
Monday 07/06/09 Friday 07/03/09
Tuesday 07/07/09 Monday 07/06/09
Wednesday 07/08/09 Tuesday 07/07/09
Thursday 07/09/09 Wednesday 07/08/09
Friday 07/10/09 Thursday 07/09/09
Saturday 07/11/09 Friday 07/10/09
Sunday 07/12/09 Friday 07/10/09
Monday 07/13/09 Friday 07/10/09
Tuesday 07/14/09 Monday 07/13/09
Wednesday 07/15/09 Tuesday 07/14/09
Thursday 07/16/09 Wednesday 07/15/09
Friday 07/17/09 Thursday 07/16/09


So instead of using a formula of the form:

=VLOOKUP(A1,some_table,some_column)

we can use:

=VLOOKUP(A1-(WEEKDAY(A1)=1)-1-2*(WEEKDAY(A1)=2),some_table,some_column)

 

[Ashish Mathur]

Hi,

Assume you have the dates in range C16:C23. Enter the date in cell G17 (in
your case, it is June 19, 2009). In cell G20, enter the following array
formula (Ctrl+Shift+Enter)

=MAX(INDEX((C16:C23<G17)*(WEEKDAY(ROW(INDIRECT(C16&":"&C23)),2)<6)*(C16:C23),,1))

This formula will give the last weekday prior to the date in cell G20.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com