VBA to interpolate between depths

[brandon20]

Hi,

I have two worksheets. Both have columns for (well name) and (Depth), one worksheet has a column for (pressure) and the other has a column for (Surface) that is associated with the well name and depth.
Simple enough.
What I need is to match up the surface from worksheet 2, with its associated pressure in worksheet 1.
The issue is that the pressure in worksheet 1 will occur at a depth in a BETWEEN the surfaces in worksheet 2.
I need the shallower (less depth) surface to populate the cell of this pressure.

It is all easier to see in this example:

Worksheet 1:
I filled in two (Surfaces) that would be populated if I had the code

WELLNAME	DEPTH	PRESSURE	SURFACE
RA-0074	8953	4185.7998
RA-0074	9000	4220
RA-0074	9028	4245
RA-0074	9110	4292
RA-0074	9165	4244.7002
RA-0074	9166	4244.6001
RA-0074	9182	4220.3999
RA-0074	9220	4238.1001	Z51CH
RA-0074	9350	4270.5	Z46CH
RA-0074	9447	4312
RA-0074	9539	4352
RA-0074	9570	4372.7998
RA-0074	9641	4539

<tbody>
</tbody>

Worksheet 2:

Wellname	DEPTH	Surface
RA-0074	9202.7	Z52_1
RA-0074	9204.9	Z51CH
RA-0074	9232.9	Z51
RA-0074	9237.3	Z48CH
RA-0074	9250.93	Z48
RA-0074	9313.2	Z46CH
RA-0074	9415.6	Z46
RA-0074	9421.4	Z44CH
RA-0074	9512.7	Z44

<tbody>
</tbody>

As you can see, Z51CH in worksheet 2 occurs at Depth 9204.9. The next surface in worksheet 2 occurs at depth 9232.9.
In worksheet 1, I filled in Z51CH at Depth 9220. This is because the Z51CH surface from worksheet 2 occurred at a depth 9204.9 and the NEXT surface (Z51) occurred at a depth 9232.9.
Worksheet 2, Z51CH surface is a range from 9204.9 to 9232.9.
Worksheet 1, pressure at depth 9220 falls BETWEEN worksheet 2 surface range 9204.9 to 9232.9
Therefore I need that pressure to be called Z51CH.


Any help would be greatly appreciated….

 

[steve the fish]

I cant help with VBA but this formula will do it. It will need adapting. I put your first table in A1:D14 and your lookup table in A17:C26 of the same worksheet. There may be easier formulas! Put this in D2:

=INDEX($C$18:$C$26,MATCH(IFERROR(LARGE($B$18:$B$26,COUNTIF($B$18:$B$26,">"&B2)+1),MIN($B$18:$B$26)),$B$18:$B$26,0))

 

[brandon20]

Hi,

Thank you so much! An equation that could do this would be much better for me as I am no expert with VBA, I can learn from an equation to adapt it to different problems in the future.

I think this one almost has it.

I noticed one issue,

When I ran the equation on multiple wells it seemed to come up with incorrect answers.
In column A I have 1000 different well names, RA-0001, RA-0100HST, RA-0500...... bla bla bla.

When I ran the equation on a single well it worked great. What would I throw into this equation to make it go through by specific well name first? I noticed it was not looking in column A.

Thanks again!

Brandon