Excel - N/a In cell causes formula searching range to break

Question

I have a formula in excel checking a range and returning only cells which contain Monday.

However, some cells in the range have an #N/A and this is causing my formula to break.

If I remove them then it works perfectly again, I don't want to have to constantly be checking for N/A's though so is there any way to prevent these from affecting my formula?

=IFERROR(INDEX(A$2:A$1000,SMALL(IF($D$2:$D$1000 = "Monday", ROW(A$2:A$1000)-ROW(A$2)+1),ROWS(A$2:A2))),"")

There is my formula in case it is something specifically to do with that. I have noticed that it only seems to break if the If statement comes across a cell with the N/A.

For example if there was one in cell A500, I can keep the index range going up to 1000, but have to change the ranges inside the If statement to end at 499.

Answer 1

You need to put the error check into the part that is using an array where you will find the error.

In your example column D is returning the Error, and that is used within the SMALL part of your formula,

So wrap the array part of SMALL into an IFERROR, and rather than return "", return a large number, as you are only looking for the SMALL, i.e 9999999 will be larger than any row in range A2:A1000.

Hope that made sense, anyways, see below for result:

{=IFERROR((INDEX(A$2:A$1000,SMALL(IFERROR(IF($D$2:$D$1000="Monday",ROW(A$2:A$1000)-ROW(A$2)+1),999999999),ROWS(A$2:A2)),1)),"")}