Forum Discussion

chris8556's avatar
chris8556
Copper Contributor
Jan 02, 2025

VLOOKUP FORMULA FOR ATTENDANCE TRACKER

I have the following attendance tracker, on the Register tab I want the shift times to be automatically populated based on the date and pulling the data from the Employee tab (sheet)

I have tried to have a go with a VLOOKUP formula but can't seem to work it out. Can it be done based on the current set up?

Thanks

Any help would be much appreciated.

https://docs.google.com/spreadsheets/d/1Imot_A0wfcQYbAGu-0ZQXH1P_Ufhskvfd8LVii5veTs/edit?usp=sharing

Various VLOOKUP formulas found on google but i'm a novice when it comes to formulas to be honest

Images:

  • Try below:

     

    1. Open your Google Sheet and navigate to the Register tab.
    2. Identify the columns where you want the shift times to appear.
    3. Use the following formula in the cell where you want the shift time to be populated:
      =VLOOKUP(A2, Employee!$A$2:$D$100, 3, FALSE)
      • A2 is the cell with the employee's name or ID in the Register tab.
      • Employee!$A$2:$D$100 is the range in the Employee tab where the data is stored. Adjust this range according to your actual data.
      • 3 is the column index number in the Employee tab that contains the shift times.
      • FALSE ensures an exact match.
    4. Drag the formula down to apply it to other cells in the column.

    If you need to match based on both the employee name/ID and the date, you might need a more complex formula using INDEX and MATCH:

    =INDEX(Employee!$C$2:$C$100, MATCH(1, (Employee!$A$2:$A$100=A2)*(Employee!$B$2:$B$100=B2), 0))
    • Employee!$C$2:$C$100 is the column with the shift times.
    • Employee!$A$2:$A$100 is the column with employee names/IDs.
    • Employee!$B$2:$B$100 is the column with dates.
    • A2 is the cell with the employee's name/ID in the Register tab.
    • B2 is the cell with the date in the Register tab.

    Make sure to press Ctrl+Shift+Enter to enter this as an array formula in Google Sheets.

    • chris8556's avatar
      chris8556
      Copper Contributor

      This is amazing thank you :) however, can this be achieved with the current set-up I have? Each line has the Week Commencing date as seen in the screenshot and then Mon - Sunday, however ideally I would want the sheet to populate the shifts for the whole week based on looking up the Week Commencing date and then the corresponding week day for that week. Hope that makes sense? Thanks 

Resources