INDEX? VLOOKUP?

[Guest]

Hey guys
,
I'm working on some data and not sure if V LOOKUP or INDEX is better to
use... i have been having problems getting either to work for me...
The data looks something like this:

A B C D
FROM TO CORRECT NEW FROM
60 65 0.4 68
65 70 1.2 73
70 80 -1.5 75
80 110 -1.6 78

what i am trying to do is find which FROM TO range the NEW FROM cells fall
within and then add the corresponding CORRECT value to it...

Any suggestions would be greatly appreciated!
Cheers,
Lloydy

 

[Fred Smith]

As long as your To cell is always equal to the next From cell (ie, b2=a3),
Vlookup will do everything you need. To add 1.2 to the 68 in D2, use:

=d2+vlookup(d2,a:d,3,true)

 

[Guest]

Thanks Fred!!
it worked perfectly, i was getting close but couldn't quite get it all to
work in one formula. Yours has fix my woes!

Cheer,
Brendan