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.

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.

 

[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