IF and LOOKUP to find value based on two data columns with repeat values?

[FlowersinExcel]

Hi,
I am trying to write a formula that will let me search two big columns of information to spit out a unique value in a third column. I have a list of names, with many repeat names, a level of service provided, and then a start & end date for the dates of service, also with many repeat dates. So I need a formula that says, if the date and the person match, give me the level of service. The problem is that I'll get multiple values for dates and multiple values for names, but only one value where those coincide. I've tried nested IFs and AND formulas, and a LOOKUP formula {LOOKUP(2,1/(start<=date)*(end>=date),Service column)}, but I don't know how to make the lookup formula work with the IF formula since I want excel to find the match. Please help - any suggestions are appreciated.

Here's what I'm trying to do. Specifically, I'm trying to figure out the formula that I can spread across and down for Step 1.

 

[Fluff]

Hi & welcome to MrExcel.

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2	Alex	5	01/01/2019	07/01/2019				01/01/2019	02/01/2019	03/01/2019	04/01/2019	05/01/2019	06/01/2019	07/01/2019	08/01/2019	09/01/2019	10/01/2019	11/01/2019	12/01/2019	13/01/2019	14/01/2019	15/01/2019	16/01/2019	17/01/2019	18/01/2019	19/01/2019
3	Alex	4	08/01/2019	16/02/2019			Alex	5	5	5	5	5	5	5	4	4	4	4	4	4	4	4	4	4	4	4
4	Alex	3	17/02/2019	18/02/2019			Sam	2	2	2	2	2	2	2	2	2	2	2	3	3	3	3	3	3	3	3
5	Alex	4	19/02/2019	04/03/2019
6	Sam	2	01/01/2019	11/01/2019
7	Sam	3	12/01/2019	23/02/2019
Wk42HS

Cell Formulas
Range	Formula
H3:Z4	H3	=IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/(($A$2:$A$100=$G3)*($C$2:$C$100<=H$2)*($D$2:$D$100>=H$2)),1)),"")

 

[DanteAmor]

Hi @FlowersinExcel, welcome to the board!

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Student	Level	StartDate	EndDate		Step 1
3	Alex	5	01/01/2019	07/01/2019		Student	01/01/2019	02/01/2019	03/01/2019	04/01/2019	05/01/2019	06/01/2019	07/01/2019	08/01/2019	09/01/2019	10/01/2019	11/01/2019
4	Alex	4	08/01/2019	16/02/2019		Alex	5	5	5	5	5	5	5	4	4	4	4
5	Alex	3	17/02/2019	18/02/2019		Sam			4	4	4	4	4	4	4	4
6	Alex	4	19/02/2019	04/03/2019		Chris					5	5	5	2	2	3	3
7	Sam	4	03/01/2019	10/01/2019
8	Sam	3	11/01/2019	10/01/2019
9	Sam	2	21/01/2019	27/02/2019
10	Chris	5	05/01/2019	07/01/2019
11	Chris	2	08/01/2019	09/01/2019
12	Chris	3	10/01/2019	14/01/2019
13	Chris	2	15/01/2019	17/01/2019
14	Chris	3	18/01/2019	31/01/2019
sheet

Cell Formulas
Range	Formula
G4:Q6	G4	=IFERROR(INDEX($B$3:$B$14,SUMPRODUCT(($A$3:$A$14=$F4)*($C$3:$C$14<=G$3)*($D$3:$D$14>=G$3)*ROW($B$3:$B$14))-ROW($A$3)+1),"")

 

[FlowersinExcel]

Yes! Thank you so much, that worked beautifully!! Thank you thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.