How to find the Nth observation from N?

[raven316]

Hello everyone,
I would like to know what is the best function or formula to use to find the Nth observation after an initial observation.
What I mean is:
Suppose I have a set of a stock closing prices.
If my initial observation is July 1, 2015, then how could I find the value that is 5 days later? or 30 days later? or any other date for that matter.
I cant just a vlookup and do July 1, 2015 and add say 5 or 30 days to it, because there are weekends and holidays to account for when the market is closed.
An example of a data set is pasted below.
I thought of using vlookup with match and index function, but not sure how.
Thank you in advance for any help.


A B
1 Date Close
2 14/08/2015 6.89
3 13/08/2015 6.94
4 12/08/2015 7.17
5 11/08/2015 7.16
6 10/08/2015 7.43
7 07/08/2015 7.68
8 06/08/2015 7.71
9 05/08/2015 7.26
10 04/08/2015 7.38
11 03/08/2015 7.23
12 31/07/2015 7.62
13 30/07/2015 7.64
14 29/07/2015 7.6
15 28/07/2015 7.44
16 27/07/2015 7.15
17 24/07/2015 7.12
18 23/07/2015 7.37
19 22/07/2015 7.46
20 21/07/2015 7.7
21 20/07/2015 7.59
22 17/07/2015 7.48
23 16/07/2015 7.17
24 15/07/2015 7.18
25 14/07/2015 7.34
26 13/07/2015 7.47
27 10/07/2015 7.5
28 09/07/2015 7.3
29 08/07/2015 7.07
30 07/07/2015 7.1
31 06/07/2015 7.4
32 02/07/2015 7.43
33 01/07/2015 7.69

 

[Peter_SSs]

Welcome to the MrExcel board!

If the "Initial Observation" date is always one of the actual dates in the list, then try the E3 formula below.

If the "Initial Observation" date is always the very bottom date in column A, then try E7

If neither of those, please provide further clarification.

Excel Workbook
	A	B	C	D	E
1	Date	Close		Start Date	31/7/2015
2	14/8/2015	6.89		Nth After	5
3	13/8/2015	6.94		Close	7.68
4	12/8/2015	7.17
5	11/8/2015	7.16
6	10/8/2015	7.43		Nth After	5
7	7/8/2015	7.68		Close	7.3
8	6/8/2015	7.71
9	5/8/2015	7.26
10	4/8/2015	7.38
11	3/8/2015	7.23
12	31/7/2015	7.62
13	30/7/2015	7.64
14	29/7/2015	7.6
15	28/7/2015	7.44
16	27/7/2015	7.15
17	24/7/2015	7.12
18	23/7/2015	7.37
19	22/7/2015	7.46
20	21/7/2015	7.7
21	20/7/2015	7.59
22	17/7/2015	7.48
23	16/7/2015	7.17
24	15/7/2015	7.18
25	14/7/2015	7.34
26	13/7/2015	7.47
27	10/7/2015	7.5
28	9/7/2015	7.3
29	8/7/2015	7.07
30	7/7/2015	7.1
31	6/7/2015	7.4
32	2/7/2015	7.43
33	1/7/2015	7.69
34
Lookup Price

 

[raven316]

Thank you for the welcome and the solution Peter. Yes, it was the E3 cell formula that did the trick.
THANK YOU VERY MUCH. BEST WISHES FROM TORONTO.

 

[Peter_SSs]

Thank you for the welcome and the solution Peter. Yes, it was the E3 cell formula that did the trick.
THANK YOU VERY MUCH. BEST WISHES FROM TORONTO.

Glad it helped. Thanks for letting us know.