Get a text value from a number range that falls within another range

[Crimzo]

Hi, I am new to excel and sorry in advance if this has been posted already, but I did not have any luck finding anything.

Using the example attached, I am looking at borehole data of the subsurface where I have one spreadsheet giving me raw data from three boreholes but no soil units (in ?), and another spreadsheet of the soil unit range I wish to assign to the first spreadsheet.

Is there a way to tell excel to look for the borehole number, in spreadsheet 1 then in spreadsheet 2. To then use the depth in spreadsheet 1 that the data falls in and assign it a soil unit based on the depth ranges given in spreadsheet 2 for that borehole, then apply this to all borehole numbers? I have tried a Vlookup function and IF function but to no success. (Depth is in m and data is arbitrary).

Any help would be much appreciated! 

[#2 · Accepted solution]

@Crimzo 

See the attached version. I moved the depth range to Sheet2. The formula in A2 on Sheet1 is

=VLOOKUP(C2,OFFSET(Sheet2!$B$1,MATCH(B2,Sheet2!A:A,0)-1,0,COUNTIF(Sheet2!A:A,B2),3),3)

This has been filled down.

[#3]

@Hans Vogelaar Thank you so much, that has worked brilliantly!