0

How to efectively deal with multiple IF-statements and with multiple "between number" ranges?
(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
1269-1800
etc...

IF()

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

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

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

*

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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