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

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!