Complex lookup (newest and oldest data points)

[DRExcel515]

In column A on sheet Calcs I have a distinct list of ticker symbols. On another tab "SQL Dump" I have a much larger data set with all the same ticker symbols but multiple occurances of the symbols (5yrs of monthly data points for each symbol). The formula I want to build starting on the Calcs tab in C2 needs to look up the most recent data point for the symbol in A2 on the SQL Dump tab as well as the oldest data point for that symbol and divide it by the number of months between the two.

Here is the actual formula if that helps..... (Most recent data point - Oldest data point) / # of Months between the two data points

On the SQL Dump tab the data points are in column D starting in row 2 and the ticker symbols are all in order of month end. The trouble I'm having is finding a way to incorporate a lookup to find me the most recent month end for that specific fund, then the oldest available month end and how many months are inbetween as some of the fund symbols may have a full five years of month end data but some may not and there may be a (null) value on some month ends.

 

[DRExcel515]

If there isn't really an easy way to build this a workaround I could use would be to look up the most recent data point in one cell then the oldest available data point for that symbol in the next cell and a 3rd cell showing the count of months between those two data points maybe.

Below is how the data is set up on the SQL Dump tab. On the other Calcs tab I would want to lookup the most recent data point (ignoring the (null) in this case below 2.4, then lookup the oldest data point (again ignoring the (null) which would be 1.85 and divide by the number of months between the two points (ignoring the (null) months and in this case that number would be 3 months. Bellow this table is how the calcs tab looks if that helps as well.

ITT	04/30/2013 <tbody> </tbody>	(null)
ITT	05/31/2013	(null)
ITT	06/30/2013	(null)
ITT	07/31/2013	1.85
ITT	08/31/2013	2.5
ITT	09/30/2013	3.2
ITT	10/31/2013	2.4

<tbody>
</tbody>

Calcs Tab:

Formula (Column C)

ITT		(2.4 - 1.85) / 3 = .1833333
HYF
HMB
ICT
TLV
MM
IBV

<tbody>
</tbody>

 

[Eric W]

Maybe:

	A	B	C	D	E	F
1	Ticker	Date	Data		Ticker	(old-new)/months
2	ABC	4/30/2013	1	a	ABC	1.8
3	DEF	4/30/2013	2	d	DEF	1.666667
4	ABC	5/31/2013	3
5	DEF	5/31/2013	4
6	ABC	6/30/2013	5
7	DEF	6/30/2013	6
8	ABC	7/31/2013	7
9	DEF	7/31/2013	8
10	ABC	8/31/2013	9
11	ABC	9/30/2013	10	a
12	DEF	9/30/2013	11
13	DEF	10/31/2013	12	d

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

SQL Dump​

Worksheet Formulas Cell Formula F2 =(LOOKUP(2,1/('SQL Dump'!$A$2:$A$13=E2),$C$2:$C$13)-VLOOKUP(E2,'SQL Dump'!$A$2:$C$13,3,0))/DATEDIF(VLOOKUP(E2,'SQL Dump'!$A$2:$C$13,2,0),LOOKUP(2,1/('SQL Dump'!$A$2:$A$13=E2),$B$2:$B$13),"m") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>