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

[Sandeep Singh]

Hi All,

Hope Everyone is Doing Great !!!

Data on which I am applying formula.

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]CA[/TD]
[TD="class: xl64, width: 64"]1-99[/TD]
[TD="class: xl63, width: 64"]DH[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]100-199[/TD]
[TD="class: xl63"]NH[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]200-299[/TD]
[TD="class: xl63"]ME[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]500-599[/TD]
[TD="class: xl63"]IJ[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]600-699[/TD]
[TD="class: xl63"]UY[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]800-899[/TD]
[TD="class: xl63"]PL[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]900-999[/TD]
[TD="class: xl63"]UJ[/TD]
[/TR]
[TR]
[TD="class: xl63"]AZ[/TD]
[TD="class: xl64"]1-64[/TD]
[TD="class: xl63"]MN[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]64-389[/TD]
[TD="class: xl63"]OP[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]390-550[/TD]
[TD="class: xl63"]LA[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]551-799[/TD]
[TD="class: xl63"]SF[/TD]
[/TR]
[TR]
[TD="class: xl63"]NH[/TD]
[TD="class: xl63"]54-95[/TD]
[TD="class: xl63"]LO[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]96-155[/TD]
[TD="class: xl63"]ER[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]156-680[/TD]
[TD="class: xl63"]CO[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]681-999[/TD]
[TD="class: xl63"]GH[/TD]
[/TR]
</tbody>[/TABLE]



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]

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?

 

[FormR]

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]

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]

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

 

[MUHAMMAD IBRAHIM]

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

it s column b (3)

 

[FormR]

On Sheet1?

Anyway - assuming it is column B, try:

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

 

[MUHAMMAD IBRAHIM]

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.

 

[MUHAMMAD IBRAHIM]

how can i be your student to learn excel from you

 

[FormR]

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]

thankyou once again because worked to with an alphabet.