IFERROR Mistake?

exceldotcom

New Member
Joined
Jul 2, 2018
Messages
15
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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:

Code:
=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:

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top