Compare two arrays(contains multiple columns and rows) and return the missing

[leprince2007]

Hello Everybody:
Data Sheet contains full list of Employees Names
In Report sheet,there are some employees names(not all of them) that are taken from Data Sheet
Question: I want a formula to get the missing employees names in Report sheet
Note: I want a short formula as possible

[JohnTopley]

In "Data"

in C2

=COUNTIF(Report!$B$2:$L$48,Data!B3)

copy down

In "Report"

C58

=IFERROR(INDEX(Data!B:B,SMALL(IF(Data!$C$2:$C$200=0,ROW($B$2:$B$200),""),ROWS($1:1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[leprince2007]

Thank you very much sir but I don`t like helping columns.
Can you found another solution without using helping columns>

[MarvinP]

Hi leprince,

I don't like helping columns either, but without using macros, I think the above solution is the shortest one possible.

If you don't like helping columns, then you might want the Data on the same sheet as the report and do a Conditional Formatting that shows duplicates on the sheet. That would keep from using a helper column. The names not formatted using CF would be your answer.

[tim201110]

=INDEX(Data!$B:$B,AGGREGATE(15,6,ROW(Data!$B$2:$B$141)/(1/(COUNTIF(Report!$B$2:$L$48,Data!$B$2:$B$141)=0)),ROWS($A$58:$A58)))

[Teethless mama]

"Note: I want a short formula as possible" --- No problem

=IFERROR(INDEX(Data,SMALL(IF(COUNTIF(Names,Data)=0,ROW(INDIRECT("1:"&ROWS(Data)))),ROWS($1:1))),"")

Array formula: Press Ctrl+Shift+Enter, not just Enter
"Data" range: B2:B41 on data sheet
"Name" range: B2:L48 on report sheet

[leprince2007]

Thank you very much Teethless mama

[Teethless mama]

Thank you very much Teethless mama

You're Welcome!