How do I highlight a cell in Excel if IFERROR(X,Y) returns #N/A for X only?

I have IFERROR(X,Y) where X and Y are formulas.

If X returns #N/A it reverts to Y.

How do I get the cell to highlight if X=#N/A , yet the cell doesn’t contain #N/A because it shows the output of Y?

Alternatively, If IFERROR(X,Y) returns Y, how do I trigger a highlight?

2 Answers

You need to do it with conditional formatting:

• Select the cell(s) you want to format
• Go to Home - Conditional formatting - New rule
• Select "Create a formula to determine which cells to format"
• Enter the formula =ISERROR(X) where X is the same as in your question
• press format and set the desired highlight
• press OK twice

I could achieve the desired result with this post. :) Thanks a lot!

I had a old data sheet, a matrix of 2000 x 150 approx and a new data sheet, a matrix of 2300 x 160. I wanted to bring the new data into the old sheet, wherever possible, without disturbing the structure of old sheet. So extra data in the new sheet is not to be used as these fields are not yet available in the downstream software. However some of the fields in old sheet is missing in the new sheet, so a decision was made to continue with the old data wherever new data is not available. So I applied

=iferror(index(newsheet____,match(_____),match(_____)),index(oldsheet____(match(____),match(_____)))

However, reply could have been a little bit more elaborate, because I failed to understand initially but with some hit & trial I could solve the issue.

So, here goes my update.

1. In the cell, write the usual =iferror(x,y) formula.
2. Open conditional formatting - "create new rule" dialogue box and select the option "Use a formula to determine which cell to format"
3. Now write the =iserror(x) formula in the dialogue box
4. Now apply this rule wherever you want.