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

Sandeep Singh

New Member
Joined
Mar 13, 2013
Messages
40
Hi All,

Hope Everyone is Doing Great !!!

Data on which I am applying formula.

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

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

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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"?
 
Upvote 0
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
 
Upvote 0
OK, good - which column on sheet1 contains the roll no?
 
Upvote 0
On Sheet1?

Anyway - assuming it is column B, try:

=VLOOKUP(B3,Sheet1!B:F,5,0)
 
Upvote 0
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.
 
Upvote 0
thankyou once again because worked to with an alphabet.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top