# IF(AND plus ISERROR

#### Ebrojie

##### New Member
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

##### Active Member
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

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.

##### Active Member
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

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:

#### Ebrojie

##### New Member

Thank you so much for your assistance. Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,816
Messages
5,855,798
Members
431,765
Latest member
RedleoUK ### 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.

### Which adblocker are you using?    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

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