Formula that can lookup in a table and return shift time for the specific day and name

Anyone knows a formula that can pull shift times located in a range based on day of the week. I have a formula that pulls the times for the specific day but the problem is, when that day change. I have to change the formula again to pull for that specific day. Is there anyway the formula can know when the day changes then the shift changes as well?

I've attached a sample file for reference....

Any help will be greatly appreciated.

It worked!!!!!!!!! thanks a million!!!!!!

Is there anyway we can tweak this same formula to pull the Name "VAC" for the name and date? Its a similar process to how the formula works above but instead I want it to pull the word "VAC" for the date and name. The data I wish to pull is in the LOA tab.

I've attached a sample file

Is it possible?

Is this to replace the formula in I4?

The formula in there already is the best option, however you do have a couple of minor errors with it (corrected below).

=INDEX('LOA PLAN'!$M$1:$AP$71,MATCH('Login Times'!H4,'LOA PLAN'!$A$1:$A$71,0),MATCH($H$1,'LOA PLAN'!$M$1:$AP$1,0))

Also you will need to correct the dates in the LOA PLAN M1:AP1, which are all for 2017, where the formula is looking for 2018.

Yep, its to replace the formula in I4... It works great. It works nicely...How do we tweak it to not ignore duplicates...For example I have 1 person listed twice on different dates. the formula does not pull once the name is located in the LOA tab twice.

Any ideas how we can fix it?

It's a bit clunky but best I can think up to meet the requirements efficiently.

=IF(COUNTIFS('LOA PLAN'!$A$3:$A$71,'Login Times'!H4,'LOA PLAN'!$C$3:$C$71,"<="&'Login Times'!$H$1,'LOA PLAN'!$D$3:$D$71,">="&'Login Times'!$H$1)=0,"-",INDEX(INDEX('LOA PLAN'!$M$3:$AP$71,0,MATCH($H$1,'LOA PLAN'!$M$1:$AP$1,0)),MATCH(2,1/IF('LOA PLAN'!$A$3:$A$71='Login Times'!H4,INDEX('LOA PLAN'!$M$3:$AP$71,0,MATCH($H$1,'LOA PLAN'!$M$1:$AP$1,0))<>""))))

Array confirmed with Shift Ctrl Enter.