How do I lookup numbers with a MIN and MAX?

[Guest]

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

 

[Guest]

JDA,
You would need an array.
Assumptions:
- Your table is in cells A14 and your data is in range A24
- 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$4>F2)*($D$2:$D$4))
When you Ctrl-Shift-Enter, it will appear as...
{=SUM(($B$2:$B$4<=F2)*($C$2:$C$4>F2)*($D$2:$D$4))}


HTH,

 

[Harlan Grove]

JDA wrote...

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:

[reformatted using underscores instead of tabs]

SubDv_____From___To____MPH
Squamish___0______3.5__15
Squamish___3.5___10.9__20
Squamish__10.9___42____25

....

If the value in the From column is always just the value from the To
column in the row above, and the From column is in ascending order, you
don't need to consider the To column, and you have a simple lookup
against the From column. Use VLOOKUP. If the entire table above were in
A14, and the MP value you wanted to lookup were in cell X99, the
formula would be

=VLOOKUP(X99,$B$2:$D$4,3)