Multiple IF statements between number ranges

Question

I'm trying to set up a formula with multiple IF statements between number ranges but I'm seeing the error:

Formula parse error

Here is the forumula:

=IF((AND(A2>0,A2<500),"Less than 500",
 If(AND(A2>=500,A2<1000),"Between 500 and 1000"),
 If(AND(A2>=1000,A2<1500),"Between 1000 and 1500"),
 If(AND(A2>=1500,A2<2000),"Between 1500 and 2000")))

Solution 1

It's a little tricky because of the nested IFs but here is my answer (confirmed in Google Spreadsheets):

=IF(AND(A2>=0,    A2<500),  "Less than 500", 
 IF(AND(A2>=500,  A2<1000), "Between 500 and 1000", 
 IF(AND(A2>=1000, A2<1500), "Between 1000 and 1500", 
 IF(AND(A2>=1500, A2<2000), "Between 1500 and 2000", "Undefined"))))

Solution 2

I suggest using vlookup function to get the nearest match.

---

Step 1

Prepare data range and name it: 'numberRange':

Select the range. Go to menu: Data → Named ranges... → define the new named range.

Step 2

Use this simple formula:

=VLOOKUP(A2,numberRange,2)

---

This way you can ommit errors, and easily correct the result.

Solution 3

standalone one cell solution based on VLOOKUP

US syntax:

=IFERROR(ARRAYFORMULA(IF(LEN(A2:A),
        IF(A2:A>2000, "More than 2000",VLOOKUP(A2:A,
 {{(TRANSPOSE({{{0;   "Less than 500"},
               {500;  "Between 500 and 1000"}},
              {{1000; "Between 1000 and 1500"},
               {1500; "Between 1500 and 2000"}}}))}}, 2)),)), )

---

EU syntax:

=IFERROR(ARRAYFORMULA(IF(LEN(A2:A);
        IF(A2:A>2000; "More than 2000";VLOOKUP(A2:A;
 {{(TRANSPOSE({{{0;   "Less than 500"}\
               {500;  "Between 500 and 1000"}}\
              {{1000; "Between 1000 and 1500"}\
               {1500; "Between 1500 and 2000"}}}))}}; 2));)); )

---

alternatives: https://webapps.stackexchange.com/questions/123729/

Solution 4

Shorter than accepted A, easily extensible and addresses 0 and below:

=if(or(A2<=0,A2>2000),"?",if(A2<500,"Less than 500","Between "&500*int(A2/500)&" and "&500*(int(A2/500)+1))) 

Solution 5

For multiple conditions that are sequential, Google sheets provides the more efficient and legible IFS formula:

   =IFS( 
     A2<500,  "Less than 500", 
     A2<1000, "Between 500 and 1000", 
     A2<1500, "Between 1000 and 1500", 
     A2<2000, "Between 1500 and 2000" )

You could also add a condition for cases less than 0 or greater than 2000, but this is basically what you asked.

View more solutions