0

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)

9
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

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

0

VLOOKUP()

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

9

where Sheet2 consists a list:

9



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

0

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

*

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.