"Advanced" VLOOKUP range

Question

I am having some problems with the problem as stated: I have a worksheet containing column A with different well names. In a second and third column, there are top and bottom depths of a geological layer plus the name of the layer. Example: Well_XYZ --- 40.02 --- 40.55 --- Layer_NAME

In another worksheet, I have my list of samples taken at/from these different boreholes as stated in the other worksheet in column A, with a specific sample depth. Example: Sample-XYZ --- 40.34

Now, I want to know, which formula (written in EXCEL cell) I could use to add the Layer_NAME to the sample-ID sheet when checking the sample depth of the depth interval of each borehole. I tried several approaches (using INDEX/MATCH, VLOOKUP) but neither of them is working correctly (or the formula is not accepted with "missing arguments" according to my programming R "logic").

As I don't want to outsource these things to R (yet) but instead improve my Excel knowledge (which uses different "schemes" and approaches as with Python or R), I would be very happy if you could help me out on this and introduce me in the world of "excel-thinking". :)

Thank you very much in advance!

Answer 1

It would help a lot to post an actual XL file online to look at, but I tried with this data:

Oh, I didn't expect that. SU turned my pasted table into an image. OK, let's use that.

Then, your other sheet is like:

The formula is:

=LOOKUP(B2,Sheet1!B:B, Sheet1!D:D)

I got this from here: https://exceljet.net/formula/lookup-value-between-two-numbers

But there are some BIG problems with this! First, since Well is the 1st column name in the 1st sheet, we have to assume that there can be more than one well in a sheet. That by itself is not a problem. The problem is that the data in the Top column needs to be sorted (ascending). So if you have another well and your data looked like this:

Then you have overlapping ranges (40.34 falls into 2 ranges) and you could get the wrong result (you'll get the last match). If you can modify your sample page to keep the well name as a separate column, you might be able to use that to "filter" the 1st sheet, and then perform that Lookup above on the result. That's a lot more complicated, but it's definitely do-able, see here and here.

Answer 2

I think you have some details missing from your question, but I assume that you must have the well name on the sample sheet. So, expanding on the other answer, you can use XLOOKUP against a filter of the well depths.

=XLOOKUP(H2,FILTER($B$2:$B$5,$A$2:$A$5=$G2),FILTER($D$2:$D$5,$A$2:$A$5=$G2),"",-1,1)

For simplicity, I've put my samples data on the same sheet.

The first FILTER is simply returning the values from column B where column A matches the well name on the current row (which is in column G). This is just an array of two items {40.02,40.55}. These are the values that XLOOKUP will be searching.

The second FILTER is returning the values from column D where column A matches the well name of the current row (again, column G). This is the two values {XYZ_1,XYZ_2}. These now correspond to the two numeric values listed above. If we match against the first of the lookup values, we'll return the first layer name, if we match against the second of the lookup values, we'll return the second layer name.

The fourth parameter of XLOOKUP is what to return if no match can be found. In our case, an empty string.

The fifth parameter is the important one to this problem. -1 is used for "Exact match or next smaller item". When we try to match any value from a sample with this list, we'll search the lookup array and find the closest match which is not larger than the value we're searching for. The last parameter tells the function which way to search. It can be omitted as 1 is default.

So, we are searching the array of two numbers above for 40.34. Since we will get the closest match that is not larger than that value, we will get 40.02, which is the first array item. As such, we will return the first array item from the second array containing the layer names - XYZ_1.

In summary, the filter helps us avoid the problem where different wells might have similar depths and the approximate lookup helps us find a "closest to" match from the filtered list.