IF(OR(ISERROR Problem

**penfold** · 02-02-2012, 08:53 AM

Hi,

PLease can you help me identify why this formula is not working?

=IF(OR(ISERROR(VLOOKUP(A302,'[January Stats_02.02.12.xls]Stats'!$A$2:$E$301,5,FALSE)),VLOOKUP(A302,'[January Stats_02.02.12.xls]Stats'!$A$2:$E$301,5,FALSE)=0),"New")

Basically, if the first VLOOKUP returns an error or the second vlookup returns a "0", I want the formula to return "New". At the moment, only the second VLOOKUP is working i.e. if the reference IS found and the value is "0" the formula returns "New" and if the reference IS found and the value is not "0" the formula returns "FALSE". However, if the reference IS NOT found, the formula returns #N/A but I want it to return "New".

Thank you

**zbor** · 02-02-2012, 08:58 AM

Maybe this:

=IF(ISERROR(1/VLOOKUP(A302,'[January Stats_02.02.12.xls]Stats'!$A$2:$E$301,5,FALSE)),"New")

**Andrew-R** · 02-02-2012, 08:59 AM

The problem is that if the reference isn't found then the value #NA will be returned, so for unfound references your formula reads:

=IF(OR(ISERROR(#NA),#NA=0),"New")

Which will return a #NA, because #NA can not be compared to a numeric value.

Try:

=IF(ISERROR(VLOOKUP(A302,'[January Stats_02.02.12.xls]Stats'!$A$2:$E$301,5,FALSE)),"New",IF(VLOOKUP(A302,'[January Stats_02.02.12.xls]Stats'!$A$2:$E$301,5,FALSE)=0,"New")