calculate start time from default matrix

[robertvdb]

This shouldn't be too difficult but I can't find it...

I have a few employees, whose working hours vary throughout the year. Their start and end times vary.

The default times are in yellow.

Now, when I enter their names in the daily log, the default times for that particular date should appear in col C and D.

Any help is appreciated.

 

[etaf]

in C11
=index(C$2:C$5,match($A11, $A$2:$A$5,0))
copy to end date D11 - should be
=index(D$2:D$5,match($A11, $A$2:$A$5,0))

and copy down

 

[robertvdb]

Thanks Etaf, but I should also involve column B.

If the date in B11 is larger than 1st February, then this affects the outcome in C11.

 

[etaf]

=INDEX(C$2:C$5,MATCH($B11,IF($A$2:$A$5=$A11,$B$2:$B$5),1))

lookup 2 criteria and 1 nearest.xlsx
	A	B	C	D
1	name	date	start	stop
2	john	1/1/23	9:00	17:00
3	bob	1/1/23	10:00	18:00
4	john	2/1/23	11:00	19:00
5	bob	2/1/23	13:00	15:00
6
7
8
9
10	name	date	start	stop
11	john	1/15/23	9:00	17:00
12	john	2/15/23	11:00	19:00
Sheet2

Cell Formulas
Range	Formula
C11:D12	C11	=INDEX(C$2:C$5,MATCH($B11,IF($A$2:$A$5=$A11,$B$2:$B$5),1))

 

[robertvdb]

Thanks Etaf, you made my day !

 

[etaf]

you are welcome