The list must be sorted in descending order on column B, as you show it, but
without the 0's in the first row.

The first formula returns 842, the 2nd 952.2.

=INDEX($A$2:$A$9,MATCH(-D1,$B$2:$B$9,-1))
=INDEX($A$2:$A$9,MATCH(-D1,$B$2:$B$9,-1)+1)

You can return the values from column B by replacing the $A$2:$A$9 with
$B$2:$B$9

On Mon, 15 Nov 2004 21:10:31 -0600, geooil
wrote:


Hi,

I have a list a numbers with two columns (depth and time). I also have
another depth (d1) value and I would like to use to look up the list and
find the nearest 2 depth values so I can use them to find the
correlating time value (t1) for (d1)

eg
d1 = 1200

Time Depth
0.00 0.00
223.40 -325.00
381.80 -525.00
551.00 -735.00
677.40 -925.00
842.00 -1175.00
952.20 -1373.80
970.00 -1425.00
1058.40 -1675.00


I would like to be able to look up the list using d1 and have it return

842.00 -1175.00
952.20 -1373.80

so I can use a linear equation to work out what the correlating time
for d1 is.

Can someone please help me work out which functions are the best to
use.

Thanks