SOLVED

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

Occasional Contributor

# 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!

2 Replies
best response confirmed by Crimzo (Occasional Contributor)
Solution

# Re: Get a text value from a number range that falls within another range

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.

# Re: Get a text value from a number range that falls within another range

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