Comparing log in times of faculty and remarks will either be Present, Late or Absent

Question

I am using Excel 2016 to manage our faculty attendance.

Last year, I was fine putting in the times manually, but I want to automate some aspects of this Excel file.

Which leads to my question: How can I compare the login time per class of the teacher and have its corresponding remark (Present, Late, or Absent)

I am working with 2 different worksheets btw.

Tried using VLOOKUP, INDEX, and even ISNA.

Answer 1

I have created a sample worksheet (input data basis class time) as in the image. I am assuming, you can get all your class data in a single table-

I have already shown the solution in the right part of image. Say you have two columns - Actual class timing, and the actual log-in time of faculty.

You can use Vlookup with range and filters -

1. Filter : This will filter the vlook-up data basis the original class time

2. Range look-up : In the vlookup, the last parameter '['range-lookup']- make it 1 to search between ranges. Your login time should be a 'time' value not a string. If it is a string, convert it to time

In the image above formulae in cell I2 is =VLOOKUP($H2, FILTER($B$2:$C$39, $A$2:$A$39=$G2),2,1)

Edit

I have put the lookup table in a different excel Book2, and changed the formula for that -

The new formula for Cell I2 is =VLOOKUP($H2, FILTER([Book2]Sheet1!$B$2:$C$39,[Book2]Sheet1!$A$2:$A$39=$G2),2,1)