Combine lookup and if, with point from range.

[RJP007]

I have two sets of example borehole data.

First, a range of depths with varying confidence levels.

[TABLE="width: 292"]
<TBODY>[TR]
[TD]ID</SPAN>
[/TD]
[TD]From depth</SPAN>
[/TD]
[TD]to depth</SPAN>
[/TD]
[TD]Confidence</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ1</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]High</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ1</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]7</SPAN>
[/TD]
[TD]High</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ1</SPAN>
[/TD]
[TD]7</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]Mod</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ1</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]16</SPAN>
[/TD]
[TD]Low</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ2</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]Mod</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ2</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]High</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


Second, point data where I would like to return values from within the range in the first data set. I would like the confidence column of the second data set to feed from the first data set, if the point depth data is within the depth range for a borehole with the same ID. Battled with all sorts of IF, AND and LOOKUP forumlas with no luck. Any help greatly appreciated.

Thanks

[TABLE="width: 217"]
<TBODY>[TR]
[TD]ID</SPAN>
[/TD]
[TD]Depth</SPAN>
[/TD]
[TD]Confidence</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ1</SPAN>
[/TD]
[TD]3.1</SPAN>
[/TD]
[TD]Forumla?</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ1</SPAN>
[/TD]
[TD]4.9</SPAN>
[/TD]
[TD]Forumla?</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ1</SPAN>
[/TD]
[TD]12.5</SPAN>
[/TD]
[TD]Forumla?</SPAN>
[/TD]
[/TR]
[TR]
[TD]PZ2


</SPAN>
[/TD]
[TD]3.3


</SPAN>
[/TD]
[TD]Forumla?


</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[ChrisR]

What about............(Enter using Ctrl + Shift + Enter)

=IF(C12:C17=F22,VLOOKUP(I22,D12:F17,3,TRUE),"")

C12:C17 = Your ID Column
F22 =the ID in your second sheet that you want to look for.
I22 = the depth you want to look for
D12:F17 = The columns From Depth, To Depth and Confidence (Ignoring the ID)

Using true would then bring the nearest match.

Just a note though, you have overlapping sections, E.g. 0-5 and then 5-10. You are best to change these to 0-4.99 , 5-9.99 etc.....Otherwise if it is exactly 5 the formula won't know where to look...........