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.