CODEWITHSUNDEEP
google-sheets
Jose Angel Cazares Torres
Jose Angel Cazares Torres

Reputation: 123

Checking to which range a value belongs in Google Sheets

I have some data in the following way

Category [Range 1_min] [Range 1_max] [Range 2_min] [Range 2_max] ...
A 120 130 ...
B 100 119 131 140 ...

I want to be able to quickly query a number and have it return the category it belongs to, for example 135 belongs to B and 121 belongs to A.

I already have a script that does this, but since there are 1000+ categories, it takes a long time to run. Is there a faster way of doing this?

Thanks.

Upvotes: 0

Views: 292

Answers (1)

basic
basic

Reputation: 11968

You can use LOOKUP:

=ArrayFormula(LOOKUP(2,1/((G2>=B2:B)*(G2<=C2:C)+(G2>=D2:D)*(G2<=E2:E)),A2:A))

enter image description here

Addition:

For more ranges you can add MMULT (not sure it's easier):

=ArrayFormula(LOOKUP(1,5/(MMULT(--(K2>={B2:B,D2:D,F2:F,H2:H}),ROW(A1:A4)^0)*MMULT(--(K2<={C2:C,E2:E,G2:G,I2:I}),ROW(A1:A4)^0)),A2:A))

some conditions:

  1. change first argument of LOOKUP to 1
  2. for second LOOKUP argument change denominator to 5 (number of cols to compare + 1)
  3. for second MMULT argument ROW(A1:A4) use row count according column count to compare (i.e. for 4 cols ->ROW(A1:A4), for 6 cols -> ROW(A1:A6) etc. )

Upvotes: 2

Related Questions