# 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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

##### 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
11
Views
261
Replies
5
Views
192
Replies
1
Views
537
Replies
11
Views
140
Replies
2
Views
137

1,171,631
Messages
5,876,552
Members
433,200
Latest member
CoolBlue_

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