IFERROR Mistake?

[exceldotcom]

Hi everyone,

I have a formula that populates Y or N based on a few criteria (also has conditional formatting that shows Y as Green and N as Red). The formula is working fine for that purpose however, when there is an error the cell populates #N/A (this is fine) but I would like the cell to be blank instead.

Original formula: =IF((VLOOKUP(AA3,$Y$127:$Z$133,2,0))+(VLOOKUP(Z3,$Y$127:$Z$133,2,0))+(VLOOKUP(AA3,$Y$127:$Z$133,2,0))+(VLOOKUP(AB3,$Y$127:$Z$133,2,0))>=3,"Y","N")

I added an IFERROR to the Original formula:

=IFERROR(IF((VLOOKUP(AA3,$Y$127:$Z$133,2,0))+(VLOOKUP(Z3,$Y$127:$Z$133,2,0))+(VLOOKUP(AA3,$Y$127:$Z$133,2,0))+(VLOOKUP(AB3,$Y$127:$Z$133,2,0))>=3,"Y","N"),"")

But it seems to make all cells (even those which should be Y or N) go blank. Can anyone spot anything obvious I'm doing wrong?

Thanks!

 

[oldbrewer]

I prefer to use =if(iserror(yourformula),"",yourformula)

 

[joeu2004]

IFERROR is preferable to ISERROR, if you are saving to an "xlsx" or "xlsm" file. But how many parentheses do you really need?

The original formula should be:

=IF(VLOOKUP(AA3,$Y$127:$Z$133,2,0)+VLOOKUP(Z3,$Y$127:$Z$133,2,0)+VLOOKUP(AA3,$Y$127:$Z$133,2,0)+VLOOKUP(AB3,$Y$12 7:$Z$133,2,0)>=3,"Y","N")

Copy-and-paste that formula from the Formula Bar into another cell, then edit the pasted formula to add your IFERROR wrapper, to wit:

=IFERROR(IF(VLOOKUP(AA3,$Y$127:$Z$133,2,0)+VLOOKUP(Z3,$Y$127:$Z$133,2,0)+VLOOKUP(AA3,$Y$127:$Z$133,2,0)+VLOOKUP(AB3,$Y$12 7:$Z$133,2,0)>=3,"Y","N"),"")

Try that in a row that returns "Y" or "N" for the first formula. Does it really return the null string ("") for the second formula? (Surprise!)

If so, I suggest that you upload an example Excel file (redacted) that demonstrates the difference to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response here. First, test the download URL, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview errors, and just download.)

Caveat: Some participants object to downloading files. But if the IFERROR formula does not work, following my procedure, the devil is in the details.