# ISERROR formula

#### eroszzz

##### New Member
Can someone please help me with this formula? I know I am doing something wrong with the punctuation near the 0 but not sure what.

Thanks!!

if(iserror(VLOOKUP(\$B9,'[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$P\$112,10,FALSE),0,(VLOOKUP(\$B9,'[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$P\$112,10,FALSE)

### 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
You are missing a bracket after the first Vlookup:

=IF(ISERROR(VLOOKUP(\$B9,'[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$P\$112,10,FALSE)),0,(VLOOKUP(\$B9,'[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$P\$112,10,FALSE)))

Thanks!!!

You are awesome! For some reason I dont remember putting that many ('s in - how quickly we forget - thank you!

If all you are trying to do is to trap for missing data in the VLOOKUP range (returning #N/A), then change ISERROR to ISNA so you only trap for this error. It is very specific checking. This will allow any other errors to show up.

Hi Seti,

I was actually going to recommend this formula, which is supposed to be more efficient according to Aladin Akyurek in this post http://www.mrexcel.com/board2/viewtopic.php?t=9893 , but I get a #Value! error, when I should be getting a zero (0). If I eliminate the external sheet references, it works as expected. I guess the workbooks have to be opened for it to not give the error? But then why with the formula I did recommend, it returned a zero. I surely don't have those workbooks in my computer system. Any thoughts?

Code:
`` =IF(COUNTIF('[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$A\$112,\$B9),(VLOOKUP(\$B9,'[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$P\$112,10,FALSE)),0)``

I think you get the 0 in the formula you recommended because the error is not #N/A so you are trapping it and explicitly returning 0, the TRUE result.

VLOOKUP won't work with closed workbooks. I think that VLOOKUP.EXT from morefunc.xll will allow this though.

NBVC said:
Hi Seti,

I was actually going to recommend this formula, which is supposed to be more efficient according to Aladin Akyurek in this post http://www.mrexcel.com/board2/viewtopic.php?t=9893 , but I get a #Value! error, when I should be getting a zero (0). If I eliminate the external sheet references, it works as expected. I guess the workbooks have to be opened for it to not give the error? But then why with the formula I did recommend, it returned a zero. I surely don't have those workbooks in my computer system. Any thoughts?

Code:
`` =IF(COUNTIF('[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$A\$112,\$B9),(VLOOKUP(\$B9,'[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$P\$112,10,FALSE)),0)``

It's actually less efficient than:

=IF(ISNUMBER(MATCH(\$B9,'[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$A\$112,0)),(VLOOKUP(\$B9,'[Ins Allocation JE.xls]FORMAT PSJE'!\$A\$5:\$P\$112,10,0)),0)

A fuller thread on the efficiency issue is:

http://www.mrexcel.com/board2/viewtopic.php?t=62102

Thank you both for your replies,

However, Aladin, in the post you pointed me to, why do you state the ..countif option is inefficient (see your quote below). Yet, in the post I pointed out to Seti, you say it is efficient. Am I confusing something?

5] Reject using either senseless

=IF(ISERROR(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue,LookupTable,ColIdx,0))

or inefficient

=IF(COUNTIF(LookupValue,INDEX(LookupTable,0,1)),VLOOKUP(LookupValue,LookupTable,ColIdx,0),0)

NBVC said:
Thank you both for your replies,

However, Aladin, in the post you pointed me to, why do you state the ..countif option is inefficient (see your quote below). Yet, in the post I pointed out to Seti, you say it is efficient. Am I confusing something?

5] Reject using either senseless

=IF(ISERROR(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue,LookupTable,ColIdx,0))

or inefficient

=IF(COUNTIF(LookupValue,INDEX(LookupTable,0,1)),VLOOKUP(LookupValue,LookupTable,ColIdx,0),0)

No wonder after timing results as reported in:

http://www.mrexcel.com/board2/viewtopic.php?t=40233

Ok, I got it now.

Replies
2
Views
145
Replies
1
Views
193
Replies
8
Views
212
Replies
5
Views
138
Replies
3
Views
228

1,196,474
Messages
6,015,432
Members
441,894
Latest member
Zululander

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