ISERROR formula

[eroszzz]

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)

 

[NBVC]

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

 

[eroszzz]

Thanks!!!

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

 

[Seti]

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.

 

[NBVC]

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?

 =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)

 

[Seti]

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.

 

[Aladin Akyurek]

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?

 =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

 

[NBVC]

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)

 

[Aladin Akyurek]

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

 

[NBVC]

Ok, I got it now.

Thanks Aladin.