Nov 02 2020 02:54 AM - edited Nov 02 2020 02:57 AM
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!
Nov 02 2020 03:24 AM
SolutionSee 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.
Nov 02 2020 06:27 AM
@Hans Vogelaar Thank you so much, that has worked brilliantly!
Nov 02 2020 03:24 AM
SolutionSee 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.