# Nested if staements

#### jdissi

##### New Member
I have a spreadsheet where I need to create a nested if statement. THe first condition is if sales are greater than or equal to 30000 the truth is 3000, if greater than or equal to 40000 than 4000, if greater than or equal to 50000 than 5000 and if greater than or equal to 60000 than 6000. Thanks.

Last edited:

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi jdissi,

Welcome to MrExcel!!

Though you can nest the statements, the LOOKUP function is better suited in this case, i.e. the following formula...

=LOOKUP(A1,{0,30000,40000,50000,60000},{0,3000,4000,5000,6000})

...will return the desired results based on the value in cell A1 (change to suit).

HTH

Robert

Thanks Robert.

I was hoping to getting the nested if formula to see what I was doing wrong. My formula is returning an error message.

OK, this should do it:

=IF(ISNUMBER(A1)=FALSE,0,IF(A1<30000,0,IF(AND(A1>=30000,A1<=39999),3000,IF(AND(A1>=40000,A1<=49999),4000,IF(AND(A1>=50000,A1<=59999),5000,6000)))))

HTH

Robert

Replies
2
Views
148
Replies
9
Views
256
Replies
1
Views
72
Replies
5
Views
166
Replies
0
Views
180

1,203,761
Messages
6,057,215
Members
444,914
Latest member
Mamun12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back