JDA,
You would need an array.
Assumptions:
- Your table is in cells A1:D4 and your data is in range A2:D4
- TT Spead entered in F2:F4
- The formula is an array (when you finish the formula, instead of pressing
ENTER, you press Ctrl-Shift-Enter
- The lookup is = the 'From' number and < the 'To' number because you
should not have 2 ranges that include the same number.

Enter this formula in G2 and copy down...
=SUM(($B$2:$B$4<=F2)*($C$2:$C$4F2)*($D$2:$D$4))
When you Ctrl-Shift-Enter, it will appear as...
{=SUM(($B$2:$B$4<=F2)*($C$2:$C$4F2)*($D$2:$D$4))}


HTH,
--
Gary Brown



"JDA" wrote:

Hi,

I'm trying to do a Vlookup type function where I would return a speed value
from one table to another, but I can't figure out how to make it work. the
source data table looks like this:

SubDv From To MPH
Squamish 0 3.5 15
Squamish 3.5 10.9 20
Squamish 10.9 42 25

and the receiving table looks like this:

Date Time MP ACT SPD TT SPD
06-Apr-2005 06:47:41 2.15 0
06-Apr-2005 09:58:09 2.15 0
06-Apr-2005 09:58:24 2.15 1
06-Apr-2005 09:58:45 2.16 2

In the column "TT SPD", I want Excel to lookup the value in MP and if it's
between the "From" and "To" columns in the source table, to return the "MPH"
value to "TTSPD". I can't use Vlookup unless I breakout the short table to a
4000 line table...I don't know how to make an array to do this...help?

Thanks in advance,

Justin