Adding one day to date based on other value

[Benjaminmin]

Hi,

I have a formula with a lot of prices each on a corresponding date.
However the date list excludes weekend and holidays.

I am now going to make a list of those dates, with the first (in cell F4) being =MAX(A4,Shell!A4,Tesco!A4,BHP!A4,Glaxo!A4), which works fine.

Is there a way that I can now get cell F5 (the one below) pick up the date in F4 and then take the next date in the list (list is A4:A7956).

The problem is that it is not a given that the date after 22/04/1997 is 23/04/1997 since that might be a holiday.

If anyone can help with this problem it would be amazing

Thanks in advance!

 

[Jonmo1]

Is the list A4:A7956 Sorted in Ascending order?

Try

=INDEX(A$4:A$7956,MATCH(F4,A$4:A$7956,0)+1)

 

[Joe4]

Rather than re-create the wheel, you may also want to take a look at the WORKDAY function, which calculates a WORKDAY some number of days away factoring in weekends and holidays (you just need to create a list of your Holidays).

 

[Benjaminmin]

Is the list A4:A7956 Sorted in Ascending order?

Try

=INDEX(A$4:A$7956,MATCH(F4,A$4:A$7956,0)+1)

Absolutely brilliant. Thank you very much for the fast and perfect answer

EDIT: @Joey4, thank you for you suggestion as well. The problem with this is that the dates and prices are imported via Bloomberg, so I couldnt apply any holiday filters, plus the dates are "overwritten" every time I import. But thank you anyway!

 

[scottylad2]

Excel Workbook
	E	F	G	H	I	J	K	L	M	N
21	Shjell		BP		Eddo		Glaxo
22	01/01/2011	3.56	01/01/2011	2.6	01/01/2011	3.09	01/01/2011	2.7	01/01/2011	3.56
23	02/01/2011	2.61	02/01/2011	3.57	02/01/2011	2.51	02/01/2011	3.75	02/01/2011	3.75
24	06/01/2011	3.64	05/01/2011	2.45	04/01/2011	3.23	06/01/2011	2.62	03/01/2011
25	09/01/2011	3.4	09/01/2011	3.11	07/01/2011	2.31	07/01/2011	2.31	04/01/2011
26	14/01/2011	3.32	11/01/2011	3.46	11/01/2011	3.8	11/01/2011	2.87	05/01/2011
27	15/01/2011	3.05	12/01/2011	2.92	12/01/2011	2.52	12/01/2011	3.84	06/01/2011	2.62
28	17/01/2011	3.05	15/01/2011	3.52	15/01/2011	2.95	13/01/2011	2.37	07/01/2011
29	22/01/2011	2.37	17/01/2011	3.55	19/01/2011	2.95	14/01/2011	2.02	08/01/2011
30	23/01/2011	2.63	22/01/2011	3.97	24/01/2011	2.49	18/01/2011	3.68	09/01/2011	3.11
31	24/01/2011	2.63	27/01/2011	3.2	27/01/2011	2.51	22/01/2011	2.14	10/01/2011
32	25/01/2011	3.5	01/02/2011	3.07	30/01/2011	3.28	24/01/2011	2.41	11/01/2011
33	27/01/2011	3.8	05/02/2011	3.79	04/02/2011	2.83	29/01/2011	2.14	12/01/2011
34	31/01/2011	2.56	06/02/2011	2.64	08/02/2011	2.5	31/01/2011	2.42	13/01/2011
Sheet1