Vlookup of a number then offset function to get data from next row down 4th column over

The object here is to input a Measure Depth (MD) and have it calculate a Total Vertical Depth (TVD). All the calculations for that are completed, just struggling with the following:

I have this array of information (A1:H9). To determine the TVD of any (MD), I need to find the two surveys (Column A) that are smaller and larger than the MD asked for.

So I key in a MD and have a cell that has a VLOOKUP function that will return the survey that is less than the depth asked for (completed). In order to get the second survey, one that is greater than the depth asked for, I need to add the CL (length between surveys(Column D)) to the value of data from the Vlookup. (or is there an equation that finds the "greater than" survey depth of the Measure Depth asked for?

So, the CL I need will always be (1) row down and (4) columns to the right of the value found from the Vlookup (column D). I think the formula should be something like the two below, however, neither works.

OFFSET(VLOOKUP(input Cell,A1:H9,1,true))(A1:H9,1,4)
or
and(OFFSET(VLOOKUP(Any Cel,A1:H9,1,true))(A1:H9,1,4)

Measured
Depth
Survey Inc Azm CL TVD VS N/S E/W

A B C D E F G H
Survey Inc Azm CL TVD VS N/S E/W
17777 88.53 359.77 31 10949.63 7114 7099 -494
17840 88.44 0.38 63 10951.29 7177 7162 -493
17885 89.67 0.74 45 10952.04 7222 7207 -493
17935 91.52 1.44 50 10951.52 7272 7257 -492
18001 90.42 0.82 66 10950.40 7338 7323 -491
18031 89.41 0.65 30 10950.44 7368 7353 -490
18061 88.57 0.21 30 10950.97 7398 7383 -490
18092 88.53 0.03 31 10951.76 7429 7414 -490
18123 88.84 0.74 31 10952.47 7460 7445 -490

So if I create a cell where I enter a MD say "18002" the following two depths and data need to be returned:

Depth MD Inc Azm CL TVD VS N/S E/W
18001 90.42 0.82 66 10950.40 7338 7323 -491 (already have a Vlookup that gives me this)
18031 89.41 0.65 30 10950.44 7368 7353 -490 (Need function to return this MD)


I figure the easiest way to get the "greater than" survey depth (18031) is to add the Course Length (CL) (Column D) to the "smaller than" survey (18001). This info always falls one row down and 4 columns to the right of the "less than" survey value.

Either my syntax is off or I am looking at this wrong. HELP!

Wow, quick response, thank you. However, both of these gives me an error of "You've entered too many arguments for this function", that is what I was getting when I was trying my formula.

Belay that last, paren issue. But it gives me #N/A

I did put the reference to the cell I am using.

This is eventually going to reference another sheet, but attached file is what I am doing.

I think the key here is that no matter what measured depth (MD)I put into A71, in column A (survey depth) the function needs to grab the survey "Less Than" and the survey depth "Greater Than" A71 for the other formulas to work. This will always change as we drill ahead so it needs to reference an ever changing "Greater Than" and "Less Than survey depth.

All these equations I did and I can't figure out how to get this aspect, totally pisses me off.

One other note, the CL in column D is the course length between surveys. So column D in the "Greater Than" survey has the distance from the "Less Than" survey. My initial thought was to find the "Greater Than" survey depth, get the CL from column D and add it to the depth of the "Less Than" survey. But I bet there is an easier way. Technically if we could do a Vlookup that would find the "Greater Than" survey that would solve our problem. I cannot seem to find a function that will do that.

Look at attached and let me know if you have any questions.

Any ideas on this problem? I attached a file on my last thread.

Brilliant, thank you so much. I am impressed by the fast response on this site.

Thank you again. Is there anything I can do for you?