# If function.. Error - the specified formula cannot be entered because it used more levels of nesting....

#### Sandeep Singh

##### New Member
Hi All,

Hope Everyone is Doing Great !!!

Data on which I am applying formula.

 CA 1-99 DH 100-199 NH 200-299 ME 500-599 IJ 600-699 UY 800-899 PL 900-999 UJ AZ 1-64 MN 64-389 OP 390-550 LA 551-799 SF NH 54-95 LO 96-155 ER 156-680 CO 681-999 GH

<tbody>
</tbody>

My If formula is ..

IF(D3="CA",IF(AND(E3>=1,E3<=99),C3,IF(AND(E3>=100,E3<=199),C4,IF(AND(E3>=200,E3<=299),C5,IF(AND(E3>=500,E3<=599),
C6,IF(AND(E3>=600,E3<=699),C7,IF(AND(E3>=800<=899),C8,IF(AND(E3>=900,E3<=999),C9,"Not InRange"))))))),IF(D3="AZ",IF(AND(E3>=1,E3<=64),C10,IF(AND(E3>=64,E3<=389),C11,IF(AND(E3>=390<=550),C12,IF(AND(E3>=551,E3<=799),C13,"Not In Range"))))))

I have many more if conditions to add on... but i am getting error "the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format".

Then I have Google it to see some other formula where i can i reduce if's.

I tried this "IF(D3="CA",LOOKUP(E3,{"1-99","100-199","200-299"},{"DH","NH","ME"})) its not working.

I need some help on this... Thanks for the help in advance.

##### New Member
That's not helping much, if B3 is "1A" which row (and why) should be returned from column F on sheet1?

Do you maybe have "1A" in a cell on sheet1 in the same row as the column F value?

this "1A" is not working it is asking me a value?

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### FormR

##### MrExcel MVP
You need to answer the questions. If B3 contains "1A" how do we know which row to return from column F on sheet1?

Do you have another column in sheet1 that contains the "Roll Numbers"?

##### New Member
yes there another column on another sheet because b3 is roll no . and yes on next sheet i mean second sheet also contain the same name"rollno." in which when i enter the roll no. for ex#1 . it actually picks rollno. 1 from the main sheet . and this goes same with the cost rate as well "column f" .. but in first sheet there is my database and second sheet i generate bills from my data base . i hope you got if still didnt . so can i send you with pics ex

#### FormR

##### MrExcel MVP
OK, good - which column on sheet1 contains the roll no?

##### New Member

OK, good - which column on sheet1 contains the roll no?
it s column b (3)

#### FormR

##### MrExcel MVP
On Sheet1?

Anyway - assuming it is column B, try:

=VLOOKUP(B3,Sheet1!B:F,5,0)

##### New Member

On Sheet1?

Anyway - assuming it is column B, try:

=VLOOKUP(B3,Sheet1!B:F,5,0)
thankyou once again because worked to with an alphabet.

##### New Member
how can i be your student to learn excel from you

#### FormR

##### MrExcel MVP
how can i be your student to learn excel from you

Glad we got there in the end one of the best ways to learn is by visiting forums like this, looking at questions that interest you and taking the time to understand the solutions posted.

##### New Member
thankyou once again because worked to with an alphabet.

Replies
2
Views
120
Replies
9
Views
131
Replies
8
Views
46
Replies
7
Views
110
Replies
0
Views
211

1,141,127
Messages
5,704,442
Members
421,349
Latest member
Santhosh3188

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