I am trying to build a check in/ check out tracker for my team using google forms and google sheets. The idea is that once Someone submits their response through google form, it comes to google sheet. There I would match the data against my existing staff list and determine who checked in and who has not. I have google forms part down but I am struggling with matching data with the existing staff list.
This is how I set that up and that is how my dataset and formula look like.
Dataset in Original tab (Check In / Check Out tab)
|Column A||Column B||Column C|
|Sep 16, 2021 08:05 AM||ABC@XYZ.com||Check In|
|Sep 16, 2021 09:01 AM||DFG@XYZ.com||Check In|
|Sep 16, 2021 04:15 PM||ABC@XYZ.com||Check Out|
desired outcome and Formula in a New tab
|Column A||Column B|
*A1 = email address
=If(AND('Check In / Check Out'!B:B = A1, 'Check In / Check Out'!D:D = Today(),'Check In / Check Out'!C:C = "Check in"), "Checked in", if(AND('Check In / Check Out'!B:B = A1, 'Check In / Check Out'!D:D = Today(),'Check In / Check Out'!C:C = "Check Out"),"Checked out", "Not Checked in"))
I tried slightly different variations of the above formula but no success. Can anyone please suggest how can I get the desired outcome? Thank you very much in advance!