# IF Statement

#### baggas

##### New Member
I am trying to assign a rating for example in an audit a certain score between a range will be allocated.

For example a score between 0 and 0.49 would be a 1A and a score between 0.50 would be 1B etc.

I have used the IF function and it seems ok up to a certain number of IF statements. Is there a limit to the number you can do?? If entered this below.

=IF(F169>10,4, IF(F169<=10,3D, IF(F169<8.99,3C, IF(F169<7.99,3B, IF(F169<6.99,3A, IF(F169<5.99,2D, IF(F169<4.99,2C, IF(F169<,3.99,2B E174(F169<2.99,2A, IF(F169<1.99,"1D", IF(F169<1.49,"1C", IF(F169<0.99,"1B", IF(F169<0.49,"1A")))))))))))))

any help would be appreciated. i seem to get an error roughly half way through?

Sean.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### baggas

##### New Member
ahh just seen a typo in my formula. it actually works fine up to the 2B.

#### Richard Schollar

##### MrExcel MVP
Hi Baggas

You can have a maximum of 7 nested IF statements. To get around this limitation, you need to use a Lookup eg:

=VLOOKUP(A1,D1:E10,2)

Where D1:E10 holds the number boundaries (col D) and col E holds the return values eg "1A" etc.

Make sense?

Richard

#### Krishnakumar

##### Well-known Member
hI,

=IF(F169<=10,LOOKUP(F169,{0,0.49,0.99,1.49,1.99,2.99,3.99,4.99,5.99,6.99,7.99,8.99},{"1A","1B","1C","1D","2A","2B","2C","2D","3A","3B","3C","3D"}),4)

HTH

Replies
2
Views
332
Replies
3
Views
3K
Replies
2
Views
973
Replies
5
Views
2K
Replies
9
Views
835

1,170,942
Messages
5,872,859
Members
432,950
Latest member
ALeXceLBr

### 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