return all values which have an error in the same column without gaps

[clh_1496]

Hi all,

Basically I have a spreadsheet with all employees hours listed, and another spreadsheet with a list of names, and a vlookup uses the pay number to look into the employee names spreadsheet and pull up who their manager is and if they staff etc.

But if Joe Bloggs is not on the employee names spreadsheet because he is a new and has just joined, it comes up as #N/A, and his hours are excluded from any pivot tables, meaning hours are sometimes missed.

Therefore, I'm looking for a formula that looks down column Q in a sheet, and if there is #N/A in that column, it then looks up column P (pay number) instead but groups them all together in a new colum without gaps?

Does anyone know how I would change the below formula I found to work for me:

=IFERROR(INDEX($A$1:$A$4,AGGREGATE(15,6,ROW($1:$4)/(ISERROR($B$1:$B$4)),ROW(1:1))),"")

Pay No.	Employee	Role	Team / Gaffer	Staff or Industrial

[#2 · Accepted solution]

@clh_1496 

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.