Vlookup date range and result based on criteria

[rahmit]

Dear friends,

I required your help for the below excel query:

The following is the data set sample:

[TABLE="width: 210"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Class. Date[/TD]
[TD] Reg.No [/TD]
[TD="align: right"]Course[/TD]
[/TR]
[TR]
[TD="align: right"]12/05/2017[/TD]
[TD="align: right"] 123456[/TD]
[TD="align: right"] ?[/TD]
[/TR]
[TR]
[TD="align: right"]19/05/2017[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]?[/TD]
[/TR]
[TR]
[TD="align: right"]30/05/2017[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]?[/TD]
[/TR]
[TR]
[TD="align: right"]02/06/2017[/TD]
[TD="align: right"]901999[/TD]
[TD="align: right"]?[/TD]
[/TR]
[TR]
[TD="align: right"]09/06/2017[/TD]
[TD="align: right"]901999[/TD]
[TD="align: right"]?[/TD]
[/TR]
[TR]
[TD="align: right"]16/06/2017[/TD]
[TD="align: right"]901999[/TD]
[TD="align: right"]?[/TD]
[/TR]
[TR]
[TD="align: right"]23/06/2017[/TD]
[TD="align: right"]901999[/TD]
[TD="align: right"]?[/TD]
[/TR]
</tbody>[/TABLE]

I require the course name based if two criteria are met:
a) Reg no
b) Data range is within category from the below data set:


Reg no From To Course
123456 1/05/2017 26/05/2017 Maths
123456 27/05/2017 31/05/2017 Geography
901999 1/05/2017 15/06/2017 Science
901999 16/06/2017 30/06/2017 History

Based on the below, the final data set with course name should be as follows:

[TABLE="width: 210"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Class. Date[/TD]
[TD] Reg.No [/TD]
[TD="align: right"]Course[/TD]
[/TR]
[TR]
[TD="align: right"]12/05/2017[/TD]
[TD="align: right"] 123456[/TD]
[TD="align: right"]Maths [/TD]
[/TR]
[TR]
[TD="align: right"]19/05/2017[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]Maths[/TD]
[/TR]
[TR]
[TD="align: right"]30/05/2017[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"] Geography[/TD]
[/TR]
[TR]
[TD="align: right"]02/06/2017[/TD]
[TD="align: right"]901999[/TD]
[TD="align: right"]Science[/TD]
[/TR]
[TR]
[TD="align: right"]09/06/2017[/TD]
[TD="align: right"]901999[/TD]
[TD="align: right"]Science[/TD]
[/TR]
[TR]
[TD="align: right"]16/06/2017[/TD]
[TD="align: right"]901999[/TD]
[TD="align: right"]History[/TD]
[/TR]
[TR]
[TD="align: right"]23/06/2017[/TD]
[TD="align: right"]901999[/TD]
[TD="align: right"]History

[/TD]
[/TR]
</tbody>[/TABLE]

Hope its clear. Thanks
Rahmit

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	Reg no	From	To	Course
2	123456	2017-05-01	2017-05-26	Maths
3	123456	2017-05-27	2017-05-31	Geography
4	901999	2017-05-01	2017-06-15	Science
5	901999	2017-06-16	2017-06-30	History
Sheet1

Book1
	A	B	C
1	Class. Date	Reg.No	Course
2	2017-05-12	123456	Maths
3	2017-05-19	123456	Maths
4	2017-05-30	123456	Geography
5	2017-06-02	901999	Science
6	2017-06-09	901999	Science
7	2017-06-16	901999	History
8	2017-06-23	901999	History
Sheet2

In C2 of Sheet2 control+shift+enter, not just enter, and copy down:

=INDEX(Sheet1!$D$2:$D$5,MATCH(B2,IF(A2>=Sheet1!$B$2:$B$5,IF(A2<=Sheet1!$C$2:$C$5,Sheet1!$A$2:$A$5)),0))