Multi-level and multi-argument Index in Google Sheets

Question

I am writing a sheet where I am trying to create a multi level Index that searches through 5 different columns with 3 pieces of data. So for example:

x = 40
y = 5000
z = 20000

Column1 | Column2 | Column3 | Column4 | Column5 | Column6
13      | 29      | 0       | 0       | 0       | Yes
30      | 870     | 0       | 0       | 0       | No
10      | 870     | 0       | 30000   | 1       | Blue
10      | 870     | 30001   | 100000  | 1       | Yes
10      | 870     | 100001  | 300000  | 1       | Unknown

Here's a sample set of my data, what I need is to compare

the variable x to columns 1 and then 2 (x must fall between these values)
variable y to columns 3 and 4 (y must fall between these values)
and then finally z to column 5 (z must be above these values)

In each of these cases I need to know if the the variable is either lower than or higher than . Finally, I need the matching data from column 6 to be returned as a result in my sheet. At the moment I have a simply IMMENSE list of nested if statements which consider all of these criteria separately but it doesn't lend itself very well to editing when changes need to be made to the values.

I've looked at every single page on the internet (every... single... page...) and can't seem to find the solution to my issue. Most solutions I have found are either using a single data point, using multiple data points against a single range or simply don't seem to work. The latest iteration I have tried is:

=INDEX('LTV Data'!$N$3:$N$10, MATCH($D$5 & $G$8 & $G$12, ARRAYFORMULA($D$5 <= 'LTV Data'!$H$3:$H$10 & $D$5 >= 'LTV Data'!$I$3:$I$10 & $G$12 <= 'LTV Data'!$J$3:$J$10 & $G$12 >= 'LTV Data'!$K$3:$K$10 & $G$8 <= 'LTV Data'!$L$3:$L$10), 0), 7)

But this only produces an error as the separate values I want to test against are concatenated and the Match can't find that string. I'm also unsure about the greater than and less than symbols as to how valid that syntax is. Is anyone able to shed some light on how I can achieve the result I need in a more elegant way than the mass of IFS, ANDS + ORs I have right now? Like I said, it works but it sure ain't pretty ;)

Thanks a bunch in advance!

ETA: So with the above variables the result I would like would be 'Blue'. This is because x falls between columns 1 and 2, y falls between columns 3 and 4 and z is higher than column 5 on the third row. This is all contained in the MATCH statement in the example code above. Please see the MATCH statement to see the comparisons I am trying to make.

So it's as I have written in the example code provided, in each case I need to test if the given variable is higher or lower than the digits in each column. — jaeger138
I have added further details to the question but essentially I have three variables, I want to test each of the variables against different columns using greater than or less than operators and if true for all five columns of a single row return the single matching result in column 6 — jaeger138

Tom Sharpe Tom Sharpe · Accepted Answer · 2016-12-15T21:05:56

You need to put the different criteria together using multiplication if you want to get the effect of an AND in an array:

=INDEX(F2:F10,MATCH(1,(A2:A10<x)*(B2:B10>x)*(C2:C10<y)*(D2:D10>y)*(E1:E10<z),0))

or

=INDEX(F2:F10,MATCH(1,(A2:A10<=x)*(B2:B10>=x)*(C2:C10<=y)*(D2:D10>=y)*(E1:E10<=z),0))

to include the equality (I have used named ranges for x, y and z).

Works in Google Sheets and (if entered as an array formula) Excel.

In Google Sheets you also have the option of using a filter

=filter(F2:F10,A2:A10<=x,B2:B10>=x,C2:C10<=y,D2:D10>=y,E2:E10<=z)

but then you aren't guaranteed to get just one row.

Multi-level and multi-argument Index in Google Sheets

1 Answers