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.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ITT[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104, align: right"]04/30/2013[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 104, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD](null)[/TD]
[/TR]
[TR]
[TD]ITT[/TD]
[TD]05/31/2013[/TD]
[TD](null)[/TD]
[/TR]
[TR]
[TD]ITT[/TD]
[TD]06/30/2013[/TD]
[TD](null)[/TD]
[/TR]
[TR]
[TD]ITT[/TD]
[TD]07/31/2013[/TD]
[TD]1.85[/TD]
[/TR]
[TR]
[TD]ITT[/TD]
[TD]08/31/2013[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]ITT[/TD]
[TD]09/30/2013[/TD]
[TD]3.2[/TD]
[/TR]
[TR]
[TD]ITT[/TD]
[TD]10/31/2013[/TD]
[TD]2.4[/TD]
[/TR]
</tbody>[/TABLE]

Calcs Tab:

Formula (Column C)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ITT[/TD]
[TD][/TD]
[TD](2.4 - 1.85) / 3 = .1833333[/TD]
[/TR]
[TR]
[TD]HYF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HMB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ICT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TLV[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IBV[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Maybe:

	A	B	C	D	E	F
Ticker	Date	Data	Ticker	(old-new)/months
a	ABC
d	DEF
a
d

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]ABC[/TD]
[TD="align: right"]4/30/2013[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1.8[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]DEF[/TD]
[TD="align: right"]4/30/2013[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1.666667[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]ABC[/TD]
[TD="align: right"]5/31/2013[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]DEF[/TD]
[TD="align: right"]5/31/2013[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]ABC[/TD]
[TD="align: right"]6/30/2013[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]DEF[/TD]
[TD="align: right"]6/30/2013[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]ABC[/TD]
[TD="align: right"]7/31/2013[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]DEF[/TD]
[TD="align: right"]7/31/2013[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]ABC[/TD]
[TD="align: right"]8/31/2013[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]ABC[/TD]
[TD="align: right"]9/30/2013[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]DEF[/TD]
[TD="align: right"]9/30/2013[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]DEF[/TD]
[TD="align: right"]10/31/2013[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

SQL Dump​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=(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")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]