# 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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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

Replies
1
Views
513
Replies
0
Views
219
Replies
7
Views
641
Replies
2
Views
500
Replies
0
Views
204

1,170,935
Messages
5,872,807
Members
432,948
Latest member
Yordi

### 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?

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