VLOOKUP or INDEX/MATCH utilizing day of the week

[Darren_workforce]

I have a spreadsheet, on which there are 5 columns. Row 1 has the day of the week and the remaining rows have start times for my team. I would like to reference these in another workbook so I can determine the time difference between when they punched in versus when their shift starts. (I don't have access to the payroll software due to HR restrictions).

Based on the actual day of the week I run this, I'd like to have a formula that locates the respective column and then generates the corresponding start time within that column. I anticipate it would require an Index/Match formula but can that be done in combination with a weekday lookup? Below is the formula I used that would pull Thursday's data but I was hoping to setup something within a macro that could automatically pull without me needing to update the column index number each day of the week. TYIA

=VLOOKUP(A2,'[Start Times.xlsx]Sheet1'!$B:$F,4,FALSE)

Monday	Tuesday	Wednesday	Thursday	Friday
9:30	9:30	10:00	10:30	9:30
9:30	9:30	12:30	9:30	10:00
9:30	9:30	9:30	9:30	9:30
11:00	9:00	8:00	8:00	8:00
8:00	8:00	8:00	9:00	8:00
8:30	9:30	8:30	8:30	11:30
10:30	9:30	9:30	9:30	8:30
9:30	9:30	9:30	9:30	9:30
9:00	9:00	9:00	9:00	9:00

 

[Darren_workforce]

I am contemplating and wouldn't be against using =TODAY() and =WEEKDAY() to reference the TODAY cell and generate the numerical day of the week. Can that be used with an INDIRECT for the column index number in the above referenced VLOOKUP to remove the need for an INDEX/MATCH formula? Would that be easier and less bulky?

 

[Cubist]

Try

=VLOOKUP(A2,'[Start Times.xlsx]Sheet1'!$B:$F,WEEKDAY(TODAY(),2),FALSE)

 

[Darren_workforce]

Try

=VLOOKUP(A2,'[Start Times.xlsx]Sheet1'!$B:$F,WEEKDAY(TODAY(),2),FALSE)

I'm not sure it's working. How should it reference the first row with the weekdays?

 

[Cubist]

I'm not sure it's working. How should it reference the first row with the weekdays?

You'll just have to wait until tomorrow to find out if your result differs from today. ... Jk.

Compare the result of the formula with the one in the OP. The result should reflect Friday (today) vs. Thursday (yesterday).
It's not necessary to reference the first row. WEEKDAY returns the corresponding column index for the VLOOKUP.

 

[Darren_workforce]

You'll just have to wait until tomorrow to find out if your result differs from today. ... Jk.

Compare the result of the formula with the one in the OP. The result should reflect Friday (today) vs. Thursday (yesterday).
It's not necessary to reference the first row. WEEKDAY returns the corresponding column index for the VLOOKUP.

ok it wasn't working for me yesterday but after an adjustment on my part, today's data appears to be generating. That's neat! I'll double check next Monday and confirm but thank you for your help on this one @Cubist