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")))
This question is related to
if-statement
google-sheets
google-sheets-formula
The answer is
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.
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/
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)))
Source: Stackoverflow.com