Forum Discussion
return all values which have an error in the same column without gaps
Do you have Microsoft 365 or Office 2021? If so, enter the following formula in the first cell where you want a "missing" pay number:
=FILTER(P:P,ISNA(Q:Q),"")
It will spill automatically to the cells below.
Otherwise, the formula that you found could be changed to
=IFERROR(INDEX($P$1:$P$1000,AGGREGATE(15,6,ROW($1:$1000)/(ISERROR($Q$1:$Q$1000)),ROW(1:1))),"")
It may have to be confirmed by pressing Ctrl+Shift+Enter. The formula can be filled down.
Do you have Microsoft 365 or Office 2021? If so, enter the following formula in the first cell where you want a "missing" pay number:
=FILTER(P:P,ISNA(Q:Q),"")
It will spill automatically to the cells below.
Otherwise, the formula that you found could be changed to
=IFERROR(INDEX($P$1:$P$1000,AGGREGATE(15,6,ROW($1:$1000)/(ISERROR($Q$1:$Q$1000)),ROW(1:1))),"")
It may have to be confirmed by pressing Ctrl+Shift+Enter. The formula can be filled down.