IF(AND plus ISERROR

[Ebrojie]

Hi Mr. Excel,

I am using this very long formula =if(and(D9<460),5,if(and(D9<=479,D9>460),4,if(and(D9=480),3,if(and(D9<=499,D9>480),2,if(and(D9>499),1))))) and I would like add an ISERROR to it. Can this be done? I have tried using =if(iserror(and(D9<460),5,if(and(D9<=479,D9>460),4,if(and(D9=480),3,if(and(D9<=499,D9>480),2,if(and(D9>499),1)),"-",and(D9<460),5,if(and(D9<=479,D9>460),4,if(and(D9=480),3,if(and(D9<=499,D9>480),2,if(and(D9>499),1)) it is returning a message that I have "entered too many arguments for this function". I am new to excel and I am teaching myself to use it. Please help me.

 

[vlady]

this could be shorter

=LOOKUP(D9,{0,461,480,481,500},{5,4,3,2,1})
for xl 2007 and above
=iferror(LOOKUP(D9,{0,461,480,481,500},{5,4,3,2,1}) ,"")
for lower
=if(iserror(LOOKUP(D9,{0,461,480,481,500},{5,4,3,2,1})),"",LOOKUP(D9,{0,461,480,481,500},{5,4,3,2,1}) )

Note: Not Tested...change the bold parts for the range

 

[Ebrojie]

Hi Vlady,

Do I need to specify the range like =iferror(lookup(D9{460,<479 to >460,480,<=499 to >=481,>499},{5,4,3,2,1}),"")? I am sorry, I am really new to this. I do not know how to assign the range.

 

[vlady]

no try the formula above then enter numbers in D9

Note: Not Tested...change the bold parts for the range

i just din,t understand this part

=if(and(D9<460),5,if(and(D9<=479,D9>460),

how if the value of D is 460??
or that should be =if(and(D9<=460) if this is the case the given lookup will work.

 

[Ebrojie]

Hi Vlady,

I forgot to add = after D9>460

What I am trying to achieve is when D9 results to an error another cell which is E9 will show the value of - (hyphen) thus I tried to use =if(iserror(and(D9<460),5,if(and(D9<=479,D9>=460),4,if(and(D9=480),3,if(and(D9<=499,D9>480),2,if(and(D9>499),1,"-")))))

The formula I used for D9 was =IF(ISERROR(VLOOKUP(C3,AHT!B1:M20,6,FALSE)),"-",VLOOKUP(C3,AHT!B1:M20,6,FALSE)) and it works.

The problem is if D9 has the value of - , I do not get - in E9

 

[Ebrojie]

Hi Vlady,

Thank you so much for your assistance.