Lookup Function help

[bradfdav]

I'm working on a better way to do attendance at our company, I'll try to explain this as best as I can, I'm hoping someone can give me some direction!

I have a master list of all of our current employees.
(Fig.A)

At the end of the week I run a report that lists every shift that was worked during the week.
(Fig.B)

What I would like is to quickly be able to see which employees were here and which were absent. I was thinking a format like this.
(Fig.C)

The idea is that if an employee's shift is found in the report (Fig.B) it would fill in the appropriate cell for that day with a "Absent" or "Present." It could also just be a red or green fill.

Any ideas?!

 

[Gerald Higgins]

How do you want to deal with people who work past midnight ? Like Chad Miller ?

 

[bradfdav]

Oh good question. For 3rd shift we view a "sunday night to monday" shift as their Monday, "monday night to tuesday" as their Tuesday. But if that creates issues then it wouldn't be a big deal to do them manually because we only have a couple employees on that shift.