Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I lookup numbers with a MIN and MAX?
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 |
#3
|
|||
|
|||
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 A1:D4, and the MP value you wanted to lookup were in cell X99, the formula would be =VLOOKUP(X99,$B$2:$D$4,3) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|