Formula to give employee role at the time of the DOS in timesheets

[ejackson02]

Roles Data

Employee ID	Start Date	End Date	Job Title
Kate Willson	1/1/2023	6/3/2023	Deputy Director
Kate Willson	6/4/2023	12/31/2040	Director
Jane Doe	1/1/2023	6/4/2023	Policy Consultant
Jane Doe	6/4/2023	12/31/2040	Senior Policy Consultant
Clay Brown	1/1/2023	6/5/2023	HR Generalist
Clay Brown	6/4/2023	12/31/2040	Senior HR Generalist
Kelly Price	1/1/2023	6/6/2023	Junior Policy Consultant
Kelly Price	6/4/2023	12/31/2040	Policy Consultant

Timesheet Data

DOS	Employee	Job Title
1/1/2023​	Kate Willson
6/4/2023​	Kate Willson
6/22/2023​	Kate Willson
6/23/2023​	Kate Willson
3/6/2023​	Jane Doe
3/7/2023​	Jane Doe
6/22/2023​	Jane Doe
6/23/2023​	Jane Doe
1/1/2023​	Clay Brown
6/4/2023​	Clay Brown
6/22/2023​	Clay Brown
6/23/2023​	Clay Brown
3/6/2023​	Kelly Price
3/7/2023​	Kelly Price
6/22/2023​	Kelly Price
6/23/2023​	Kelly Price

I would like the role at the time of the DOS as a third column in the timesheet data.

 

[ExceLoki]

try this
--------------------

Book1
	A	B	C	D	E	F	G	H	I
1	Employee ID	Start Date	End Date	Job Title			DOS	Employee	Job Title
2	Kate Willson	1/1/2023	6/3/2023	Deputy Director			1/1/2023	Kate Willson	Deputy Director
3	Kate Willson	6/4/2023	12/31/2040	Director			6/4/2023	Kate Willson	Director
4	Jane Doe	1/1/2023	6/4/2023	Policy Consultant			6/22/2023	Kate Willson	Director
5	Jane Doe	6/4/2023	12/31/2040	Senior Policy Consultant			6/23/2023	Kate Willson	Director
6	Clay Brown	1/1/2023	6/5/2023	HR Generalist			3/6/2023	Jane Doe	Policy Consultant
7	Clay Brown	6/4/2023	12/31/2040	Senior HR Generalist			3/7/2023	Jane Doe	Policy Consultant
8	Kelly Price	1/1/2023	6/6/2023	Junior Policy Consultant			6/22/2023	Jane Doe	Senior Policy Consultant
9	Kelly Price	6/4/2023	12/31/2040	Policy Consultant			6/23/2023	Jane Doe	Senior Policy Consultant
10							1/1/2023	Clay Brown	HR Generalist
11							6/4/2023	Clay Brown	HR Generalist
12							6/22/2023	Clay Brown	Senior HR Generalist
13							6/23/2023	Clay Brown	Senior HR Generalist
14							3/6/2023	Kelly Price	Junior Policy Consultant
15							3/7/2023	Kelly Price	Junior Policy Consultant
16							6/22/2023	Kelly Price	Policy Consultant
17							6/23/2023	Kelly Price	Policy Consultant
index match between dates

Cell Formulas
Range	Formula
I2:I17	I2	=INDEX($D$2:$D$9,MATCH(1,INDEX((H2=$A$2:$A$9)*(G2>=$B$2:$B$9)*(G2<=$C$2:$C$9),0,1),0))

 

[ejackson02]

Hello ExceLoki,

Thank you for your reply. I copied and pasted your solution into a black excel workbook and the resultant I get are #NA's. Did I miss something?

 

[ExceLoki]

i see in your profile you use several versions of office, which one did you paste it into?

 

[ejackson02]

Good point! Microsoft 365.

 

[bscoggin]

Based on what I have tested in MS 365, the following formula should work:

=INDEX($D$2:$D$9,MATCH(1,(H2=$A$2:$A$9)*(G2>=$B$2:$B$9)*(G2<=$C$2:$C$9),0))

 

[ExceLoki]

usually the first things i'd check are extra space in the data or the cell formats- making sure they match, but if you copied and pasted into a new workbook... i'm not sure why it's not working

 

[ejackson02]

Based on what I have tested in MS 365, the following formula should work:

=INDEX($D$2:$D$9,MATCH(1,(H2=$A$2:$A$9)*(G2>=$B$2:$B$9)*(G2<=$C$2:$C$9),0))

Yes, success!!!! The formula worked Thank you!