I've read a few similar questions, and I can't seem to find exactly what I'm trying to do.

I have a roster of employees in sheet "Roster" with their names in Column A. In sheet "Hours" I have a list of assigned jobs for tomorrow, with the assigned employee's name also in Column A. I'm trying to add a column of employees from the roster that are NOT in the list of employees on jobs.

The closest I've gotten is with this, on the Hours sheet:

=ARRAYFORMULA(VLOOKUP('Roster'!A2:A, A2:A,1,0))

which gives me a list of the entire roster, with the missing ones returning an #N/A error that tells me the missing name when I mouse over it and read the error code. Is there a way to just get a list of the errors? Would I be better off attacking this from a completely different angle?

EDIT: Sanitized example pictures. If what I was trying to do worked, it would return Bob and Jim in this example.

CodePudding user response：

Assuming you're trying to return this list in the "Hours" sheet, you can build off what you had. Try this:

=ARRAYFORMULA(FILTER(A2:A,ISERROR(VLOOKUP('Roster'!A2:A, A2:A,1,0))))

Keep in mind that this formula was written sight-unseen. If it doesn't work as expected, consider sharing a link to a copy of your sheet (or to a sample sheet set up the same way and with enough sanitized but realistic data to illustrate the problem, along with the manually entered result you want in the range where you want it).

CodePudding user response：

I ended up going a completely different route. I made a third "Under the Hood" sheet, pulled the two columns into it with queries, ran a match formula down the list and returned "" on errors, then ran a query on Hours to get the names where it had null for the match list.