multiple IF statements with between number ranges alternative

Question

How to efectively deal with multiple IF-statements and with multiple "between number" ranges
in Google Sheets' spreadsheet (e.g. C column based on B column)

with a rule set as follows:

01-10
11-20
21-50
51-100
101-150
151-200
201-300
301-500
501-800
801-1000
1001-1222
1223-1568
1569-1800
etc...

---

IF()

=IF(AND(B2>=1;    B2<10);     "01-10"; 
 IF(AND(B2>=11;   B2<21);     "11-20"; 
 IF(AND(B2>=21;   B2<51);     "21-50"; 
 IF(AND(B2>=51;   B2<101);    "51-100";
 IF(AND(B2>=101;  B2<151);   "101-150"; 
 IF(AND(B2>=151;  B2<201);   "151-200"; 
 IF(AND(B2>=201;  B2<301);   "201-300";
 IF(AND(B2>=301;  B2<501);   "301-500"; 
 IF(AND(B2>=501;  B2<801);   "501-800"; 
 IF(AND(B2>=801;  B2<1001);  "801-1000";
 IF(AND(B2>=1001; B2<1223); "1001-1222"; 
 IF(AND(B2>=1223; B2<1569); "1223-1568"; 
 IF(AND(B2>=1569; B2<1800); "1569-1800";)))))))))))))

• disadvantages of a standard IF statement nestings are:
  • long syntax
  • per single row dependency
  • tricky post-editing
  • tricky array formula support

---

IFS()

=IFERROR(ARRAYFORMULA(
 IFS(B2:B>=1569; "1569-1800";
     B2:B>=1223; "1223-1568";
     B2:B>=1001; "1001-1222"; 
     B2:B>=801;   "801-1000";
     B2:B>=501;   "501-800";
     B2:B>=301;   "301-500";
     B2:B>=201;   "201-300";
     B2:B>=151;   "151-200";
     B2:B>=101;   "101-150";
     B2:B>=51;     "51-100";
     B2:B>=21;     "21-50";
     B2:B>=11;     "11-20";
     B2:B>=1;      "01-10"));)

• disadvantages of IFS statement are:
  • long syntax
  • tricky post-editing
  • reverse order logic dependency

---

Answer 1

CHOOSE() with MATCH()

=ARRAYFORMULA(IFERROR(CHOOSE(MATCH(B2:B,
{1, 11, 21, 51, 101, 151, 201, 301, 501, 801, 1001, 1223, 1569}),
"01-10",
"11-20",
"21-50",
"51-100",
"101-150",
"151-200",
"201-300",
"301-500",
"501-800",
"801-1000",
"1001-1222",
"1223-1568",
"1569-1800"), ))

https://stackoverflow.com/questions/54662726/

Answer 2

standalone single cell solution using VLOOKUP()

=IFERROR(ARRAYFORMULA(VLOOKUP(B2:B, {{1,      "01-10"  };
                                     {11,     "11-20"  };
                                     {21,     "21-50"  };
                                     {51,     "51-100" };
                                     {101,   "101-150" };
                                     {151,   "151-200" };
                                     {201,   "201-300" };
                                     {301,   "301-500" };
                                     {501,   "501-800" };
                                     {801,   "801-1000"};
                                     {1001, "1001-1222"};
                                     {1223, "1223-1568"};
                                     {1569, "1569-1800"}}, 2)), )

Answer 3

ARRAYFORMULA() with IF()

=ARRAYFORMULA(IFERROR(
 IF((B2:B>=1)    * (B2:B<11),     "01-10", 
 IF((B2:B>=11)   * (B2:B<21),     "11-20", 
 IF((B2:B>=21)   * (B2:B<51),     "21-50", 
 IF((B2:B>=51)   * (B2:B<101),    "51-100",
 IF((B2:B>=101)  * (B2:B<151),   "101-150", 
 IF((B2:B>=151)  * (B2:B<201),   "151-200", 
 IF((B2:B>=201)  * (B2:B<301),   "201-300",
 IF((B2:B>=301)  * (B2:B<501),   "301-500", 
 IF((B2:B>=501)  * (B2:B<801),   "501-800", 
 IF((B2:B>=801)  * (B2:B<1001),  "801-1000",
 IF((B2:B>=1001) * (B2:B<1223), "1001-1222", 
 IF((B2:B>=1223) * (B2:B<1569), "1223-1568", 
 IF((B2:B>=1569) * (B2:B<1800), "1569-1800", ))))))))))))), ))

Answer 4

VLOOKUP()

=IFERROR(ARRAYFORMULA(VLOOKUP(B2:B;
 {QUERY(IFERROR(ARRAYFORMULA(VALUE(
  REGEXEXTRACT(Sheet2!A2:A; "(\d+)"))); ); "SELECT Col1")\
  QUERY(Sheet2!A2:A; "SELECT A")}; 2)); )

where Sheet2 consists a list:

---

---

US syntax:

=IFERROR(ARRAYFORMULA(VLOOKUP(B2:B,
 {QUERY(IFERROR(ARRAYFORMULA(VALUE(
  REGEXEXTRACT(Sheet2!A2:A, "(\d+)"))), ), "SELECT Col1"),
  QUERY(Sheet2!A2:A, "SELECT A")}, 2)), )

Answer 5

=query() is useful for this.

1. Split the "rule set" into lower and upper limits, and create a named range to contain all three resulting columns (here named "rule_set"):

To find which range the number belongs, C2 contains the following:

=query(rule_set,"select G where I>=" & B2 & " limit 1")

• rule_set is the named range we are querying
• Column G of the named range contains the number range to return
• Column I of the named range is the upper limit
• B2 is the value we're trying to match to a number range
• "limit 1" restricts the result set to one item only. Given these are ascending values, the lowest matching value is returned.

Drawbacks: =query() cannot be used with =arrayformula(), so you have to repeat this formula in every row containing values you're matching to a range.