Merging data?

This is an interesting one, I suspect you'll need a macro for it but thought I'd try here first.

Attached is an example, I require the yellow to be filled in by either SILT,SAND or CLAY

The left table is equipment push start and end depths and the right table is geological start and end depths.

What I want is the left table (yellow) to be automatically filled in by the information from the table on the right from best fitting the start and end depths. It doesn't have to be exact as the equipment pushes are not based on geology.

Is this kind of thing even possible?

Sounds like you want a LOOKUP formula.

IN E2 and filled down
=LOOKUP(C2,$K$7:$N$18)

However, your ranges need to be adjusted.
E18 returns SAND instead of SILT because the value is 35.5, which exists in both ranges specified in rows 16 and 17 of the table..

The END range should not be the same as the Start of the next range...
ie

0 - 2.027
2.027 - 5

Should be either
0 - 2.026
2.027 - 5

or
0 - 2.027
2.028 - 5

Thank you for the reply!

It almost works, I see what you mean about E18. Is there a way for this formula to detect if one of the "pushes" has a higher percentage of SAND or SILT (or CLAY) and then select that one?

Thanks again, this will get me started

Hi AlKey,

I only get an error unfortunately when I try this one

AlKey,

Sorry I just needed to more K$7:N$18 to L$7:O$18 and it works! I get the same result as LOOKUP(D2,$K$7:$N$18)

Ok What about if there are two locations?

I've added to the sheet with your solutions but I'm unsure on how to proceed

Thanks again for your help

Try

=LOOKUP(MAX(C$2:D2),OFFSET($L$7:$O$7,MATCH(A2,$K$7:$K$28,0)-1,0,COUNTIF($K$7:$K$28,A2),))

Almost, It works for the first Borehole....But then says it's SAND for everything in Borehole 2

Not sure why that $ got put into the range used by MAX, but it should be removed..

=LOOKUP(MAX(C$2:D2),OFFSET($L$7:$O$7,MATCH(A2,$K$7:$K$28,0)-1,0,COUNTIF($K$7:$K$28,A2),))
should be
=LOOKUP(MAX(C2:D2),OFFSET($L$7:$O$7,MATCH(A2,$K$7:$K$28,0)-1,0,COUNTIF($K$7:$K$28,A2),))

Not sure I understand why Max is there at all.. Maybe just use D2 (use END instead of the Start that I used at first)

=LOOKUP(D2,OFFSET($L$7:$O$7,MATCH(A2,$K$7:$K$28,0)-1,0,COUNTIF($K$7:$K$28,A2),))

Thank you,

I might need a macro to be more precise but this has helped a lot. Thank you for your solutions

You're welcome.