VLOOPUP to compare 2 spreadsheets

[Mojosixx]

I need to compare 2 spreadsheets and identify the employees missing from one of them. The first spreadsheet is a list of our employees. The second spreadsheet is a list of employees that attended a training in October 2017. Both spreadsheets include the employees unique badge number. Can I use VLOOPUP to identify what employees did not attend?

 

[Aladin Akyurek]

Here is the current set-up.

Worksheet 1 titled "Employees" is a list of all employee badge numbers starting in A2.
Worksheet 2 titled "Attendance" is a list of the badge numbers for the employees who attended the training, starting in A2 as well.

In cell B2 of worksheet one I pasted the following formula:

=1-ISNUMBER(MATCH($A2,Sheet2!A:A,0))

Do one of these work for you?

1) Control+shift+enter, not just enter:

=1-ISNUMBER(MATCH($A2,TRIM(Sheet2!A:A),0))


2)

=1-ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($A2,Sheet2!A:A,0)))

 

[Mojosixx]

Do you have any trailing spaces or "half spaces"? try =TRIM(A2) on both columns and repaste the data in.

I just did the =TRIM(A2) on each column and one still shows leading zeros; the other does not.

 

[Aladin Akyurek]

See also post #11 .