Advanced Function To Compare Two Excel Lists

[kmmkaz]

Good afternoon, was curious if someone could assist me. I am a payroll administrator and use excel quite often and looking to be more efficient.

What I am specifically looking for assistance with is my weekly payroll count. I have a template with my contractor names in one column and next to it I have an empty column that I place an “X” next to their names if the contractor has submitted a timesheet. Generally after I complete my audit, I simply filter the column to show me any missing contractor’s timesheets that I then email them requesting them to turn it in.

Every Monday morning I receive an Excel report from Corporate showing who has turned in their timesheets so I am wondering if there is an advanced function, such a VLOOKUP, that I can compare the two lists to help expedite my template to reflect who has turned in a timesheet? At the moment I just compare the two lists and side by side and make my own notations which takes forever.

Any help would be greatly appreciated.

 

[estevaoba]

Try this in a blank column in your template, next to contractors list:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IF(ISNA(MATCH(Corporate!A2,Template!$A$2:$A$1000,0)),Corporate!A2,"")[/TD]
[/TR]
</tbody>[/TABLE]