How to look up "time in" based on employee name and date of login

[thealndyn]

how to get time in based from employees name and based from the date of login using formula in excel.

what formula should i used? the data is from a different sheet.
Lets say I have Sheet 1. where I used data validation to choose the employee name. let say B4 is where the cell of the employee name.
on the same sheet. I have cell B10 for the date which is "Oct 21" and C10 for "Time in"


Sheet 2 - where the raw data is located.
Column A - Name of Employee
Column C - Date
Column D - Time in

Can you help me on what formula to use in Sheet 1 in able to get the time in of employee in specific dates.
Thank you

[CARACALLA]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[thealndyn]

here is the sample file.
I want to see whenever I change the name of the employee. I want to see their data from below .

[Gregb11]

Try this in Sheet1, cell c7 and copy down

=XLOOKUP($B$1&B7,Sheet2!$A$2:$A$3&Sheet2!$C$2:$C$3,Sheet2!$D$2:$D$3,"",0)

Your profile shows MS Office version as MS Windows 10. This is your operating system, not Excel version. Your excel version would be something like 2013, 2016, 2019, MS365, etc.

If you don't have Office 365, then the above formula will probably not work.

[thealndyn]

it worked. Thank you so much!

[Gregb11]

You're welcome.
Please update your profile.

[thealndyn]

Hi. Question with regards to this formula.

=XLOOKUP($B$1&B7,Sheet2!$A$2:$A$3&Sheet2!$C$2:$C$3,Sheet2!$D$2:$D$3,"",0)

will this work if the employee did a "DOUBLE SHIFT" in one day?

it seems xlookup will just look the first entry. how about the second one?

[Gregb11]

Yes, XLOOKUP brings back the first entry it finds. Please mock up a sample spreadsheet to show how you would want it to look if they worked a double shift. (There's only 1 cell for "Time In" per day.