# multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges

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

# 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)), )

# 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)), )

# 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", ))))))))))))), ))

# 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"), ))