I have used index/match before to match and extract data, but I can't seem to wrap my head around
this one. It's probably best explained by looking at the attached workbook.
Basically, I want formulas in 'John
Doe!B' and 'Jane Doe!B' to look up the Name and Date values in column A on the 'Data' sheet, and then return the time value
that is on the same row but in the 'Data!B' column.
I then want 'John Doe!C' and 'Jane Doe!C' to return the time
that is two rows below the time shown in 'John Doe!B' - I've gone and manually entered and bolded the values in the workbook
to show what I eventually want the formula to do.
The data on the 'Data' tab will be extracted monthly into excel
from tracking software my company uses, but there will always be a minimum of 3 rows of time for each date. As you can see in
the example workbook, some dates will be omitted for some people, and while there will always be 3 minimum rows there will
often be many more time entry rows for different dates as well. However, I'm only concerned with the 1st row and 3rd row for
I was originally thinking a vlookup for 'John Doe!B' and then a hlookup for 'John Doe!C' that
references the value in B might work, but I'm getting stuck on how to ensure the formula realizes the differences between the
data and dates for John Doe and Jane Doe.
Any help would be greatly appreciated - thank you in advance!