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 gt 0 A2 lt 500   Less than 500    If AND A2 gt  500 A2 lt 1000   Between 500 and 1000     If AND A2 gt  1000 A2 lt 1500   Between 1000 and 1500     If AND A2 gt  1500 A2 lt 2000   Between 1500 and 2000

User · Answer

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.

User · Answer

Shorter than accepted A  easily extensible and addresses 0 and below    if or A2 lt  0 A2 gt 2000      if A2 lt 500  Less than 500   Between   amp 500 int A2 500  amp   and   amp 500  int A2 500  1

User · Answer

It s a little tricky because of the nested IFs but here is my answer  confirmed in Google Spreadsheets     IF AND A2 gt  0     A2 lt 500     Less than 500     IF AND A2 gt  500   A2 lt 1000    Between 500 and 1000     IF AND A2 gt  1000  A2 lt 1500    Between 1000 and 1500     IF AND A2 gt  1500  A2 lt 2000    Between 1500 and 2000    Undefined

User · Answer

standalone one cell solution based on VLOOKUP US syntax   IFERROR ARRAYFORMULA IF LEN A2 A           IF A2 A gt 2000   quot More than 2000 quot  VLOOKUP A2 A      TRANSPOSE    0     quot Less than 500 quot                    500    quot Between 500 and 1000 quot                     1000   quot Between 1000 and 1500 quot                    1500   quot Between 1500 and 2000 quot          2           EU syntax   IFERROR ARRAYFORMULA IF LEN A2 A           IF A2 A gt 2000   quot More than 2000 quot  VLOOKUP A2 A      TRANSPOSE    0     quot Less than 500 quot                    500    quot Between 500 and 1000 quot                     1000   quot Between 1000 and 1500 quot                    1500   quot Between 1500 and 2000 quot          2           alternatives  https   webapps stackexchange com questions 123729

[if-statement] Multiple IF statements between number ranges

Step 1

Step 2

Examples related to if-statement

Examples related to google-sheets

Examples related to google-sheets-formula