SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1843867%22%20slang%3D%22en-US%22%3EGet%20a%20text%20value%20from%20a%20number%20range%20that%20falls%20within%20another%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1843867%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20new%20to%20excel%20and%20sorry%20in%20advance%20if%20this%20has%20been%20posted%20already%2C%20but%20I%20did%20not%20have%20any%20luck%20finding%20anything.%3C%2FP%3E%3CP%3EUsing%20the%20example%20attached%2C%20I%20am%20looking%20at%20borehole%20data%20of%20the%20subsurface%20where%20I%20have%20one%20spreadsheet%20giving%20me%20raw%20data%20from%20three%20boreholes%20but%20no%20soil%20units%20(in%20%3F)%2C%20and%20another%20spreadsheet%20of%20the%20soil%20unit%20range%20I%20wish%20to%20assign%20to%20the%20first%20spreadsheet.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20tell%20excel%20to%20look%20for%20the%20borehole%20number%2C%20in%20spreadsheet%201%20then%20in%20spreadsheet%202.%20To%20then%20use%20the%20depth%20in%20spreadsheet%201%20that%20the%20data%20falls%20in%20and%20assign%20it%20a%20soil%20unit%20based%20on%20the%20depth%20ranges%20given%20in%20spreadsheet%202%20for%20that%20borehole%2C%20then%20apply%20this%20to%20all%20borehole%20numbers%3F%20I%20have%20tried%20a%20Vlookup%20function%20and%20IF%20function%20but%20to%20no%20success.%20(Depth%20is%20in%20m%20and%20data%20is%20arbitrary).%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20much%20appreciated!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1843867%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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

@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.

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