IF(AND plus ISERROR

Ebrojie

New Member
Joined
Jan 17, 2014
Messages
4
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

vlady

Active Member
Joined
Jan 26, 2012
Messages
413
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

New Member
Joined
Jan 17, 2014
Messages
4
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

Active Member
Joined
Jan 26, 2012
Messages
413
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

New Member
Joined
Jan 17, 2014
Messages
4
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

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