Lag dates

[Texas Longhorn]

Hi all,

I have a sheet with some dates for company filed reports. I am trying to make the data point-in-time (i.e. where I can reference data based on when it was known as opposed to the period it covers). Here is an example of what I'm doing:

Book3
	A	B	C	D	E	F
7	MONTHEND	SYMBOL	ADJ_FISCAL_DATE	ADJ_REPORT_DATE	LagDate	Desired
8	2/28/2006	45950610	12/31/2005	1/26/2006
9	3/31/2006	45950610	3/31/2006	4/26/2006	1/26/2006	1/26/2006
10	4/30/2006	45950610	3/31/2006	4/26/2006	4/26/2006	4/26/2006
11	5/31/2006	45950610	3/31/2006	4/26/2006	4/26/2006	4/26/2006
12	6/30/2006	45950610	6/30/2006	7/26/2006	4/26/2006	4/26/2006
13	7/31/2006	45950610	6/30/2006	7/26/2006	7/26/2006	7/26/2006
14	8/31/2006	45950610	6/30/2006	7/26/2006	7/26/2006	7/26/2006
15	9/30/2006	45950610	9/30/2006	11/1/2006	7/26/2006	7/26/2006
16	10/31/2006	45950610	9/30/2006	11/1/2006	11/1/2006	7/26/2006
17	11/30/2006	45950610	9/30/2006	11/1/2006	11/1/2006	11/1/2006
18	12/31/2006	45950610	12/31/2006	1/30/2007	11/1/2006	11/1/2006
19	1/31/2007	45950610	12/31/2006	1/30/2007	1/30/2007	1/30/2007
20	2/28/2007	45950610	12/31/2006	1/30/2007	1/30/2007	1/30/2007
21	3/31/2007	45950610	3/31/2007	5/3/2007	1/30/2007	1/30/2007
22	4/30/2007	45950610	3/31/2007	5/3/2007	5/3/2007	1/30/2007
23	5/31/2007	45950610	3/31/2007	5/3/2007	5/3/2007	5/3/2007
24	6/30/2007	45950610	6/30/2007	8/7/2007	5/3/2007	5/3/2007
25	7/31/2007	45950610	6/30/2007	8/7/2007	8/7/2007	5/3/2007
26	8/31/2007	45950610	6/30/2007	8/7/2007	8/7/2007	8/7/2007
27	9/30/2007	45950610	9/30/2007	10/31/2007	8/7/2007	8/7/2007
28	10/31/2007	45950610	9/30/2007	10/31/2007	10/31/2007	10/31/2007
29	11/30/2007	45950610	9/30/2007	10/31/2007	10/31/2007	10/31/2007
30	12/31/2007	45950610	12/31/2007	1/30/2008	10/31/2007	10/31/2007
31	1/31/2008	45950610	12/31/2007	1/30/2008	1/30/2008	1/30/2008
32	4/30/2007	59987432	3/31/2007	5/3/2007
33	5/31/2007	59987432	3/31/2007	5/3/2007	5/3/2007	5/3/2007
34	6/30/2007	59987432	6/30/2007	8/7/2007	5/3/2007	5/3/2007
35	7/31/2007	59987432	6/30/2007	8/7/2007	8/7/2007	5/3/2007
36	8/31/2007	59987432	6/30/2007	8/7/2007	8/7/2007	8/7/2007
37	9/30/2007	59987432	9/30/2007	10/31/2007	8/7/2007	8/7/2007
38	10/31/2007	59987432	9/30/2007	10/31/2007	10/31/2007	10/31/2007
39	11/30/2007	59987432	9/30/2007	10/31/2007	10/31/2007	10/31/2007
40	12/31/2007	59987432	12/31/2007	1/30/2008	10/31/2007	10/31/2007
Sheet1

In column F, I've highlighted in yellow the instances where my simple formula in column E has failed and put in my desired result. Basically, if the value in column D is less than or equal to the value in column A, I want to use the value in column D (I've got that part); if the value in D is greater than the value in A, I want to pull in the date which meets these three criteria: 1. It is less than or equal to the value in A; 2. Of the dates for which criteria 1 is true, I want the date which is smaller by the least amount; and 3. The date has to be for the same symbol (column B).

Any help would be much appreciated. I'm thinking of adding a column for the difference between column D and A, but I'm not sure yet how to use that.

Please let me know if I can clarify anything.

Thanks,

Bill