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.

 

[FormR]

Or a non volatile alternative:

=INDEX(Sheet1!$F$2:$F$1000,B3)

 

[MUHAMMAD IBRAHIM]

youre absoultely genious out this world thankyou so much god bless you. if you are a teacher can you teach me how to operate a good excel. once again thankyou so so so much.

 

[MUHAMMAD IBRAHIM]

dear FormR ,
WHAT IF there is an alphabet in my index column. it is accepting only number in my index column

 

[FormR]

WHAT IF there is an alphabet in my index column. it is accepting only number in my index column

Do you mean there is a text value in B3 - if so what do you want to happen?

 

[MUHAMMAD IBRAHIM]

yes

exactly

 

[FormR]

yes

exactly

And what is the text value and what would you like to happen? It may help to provide a small example of the value in B3 and how that relates to the value that should be returned from column F on sheet1.

 

[MUHAMMAD IBRAHIM]

for example b3 is column and its name is "roll no" . and the roll no might be for ex# 1,2,3 OR 1A OR AB ETC.

 

[MUHAMMAD IBRAHIM]

COLUMN F name is "rate" which TELLS me the rate of that roll no.

 

[FormR]

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?

 

[MUHAMMAD IBRAHIM]

acctually i am making a stock in which sheet one is confidential . when i enter the given rollno it provides my data from the confidential sheet so it cannot be changed , can you plzz tell a solution of this that how cAN I ENTER A CHARACTER in my index column