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.
 

MUHAMMAD IBRAHIM

New Member
Joined
Apr 28, 2015
Messages
37
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
Joined
Aug 18, 2011
Messages
6,555
Office Version
  1. 365
Platform
  1. Windows
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"?
 

MUHAMMAD IBRAHIM

New Member
Joined
Apr 28, 2015
Messages
37
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
Joined
Aug 18, 2011
Messages
6,555
Office Version
  1. 365
Platform
  1. Windows
OK, good - which column on sheet1 contains the roll no?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,555
Office Version
  1. 365
Platform
  1. Windows
On Sheet1?

Anyway - assuming it is column B, try:

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

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,555
Office Version
  1. 365
Platform
  1. Windows
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.
 

MUHAMMAD IBRAHIM

New Member
Joined
Apr 28, 2015
Messages
37
thankyou once again because worked to with an alphabet.
 

Forum statistics

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