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:

Class. Date	Reg.No	Course
12/05/2017	123456	?
19/05/2017	123456	?
30/05/2017	123456	?
02/06/2017	901999	?
09/06/2017	901999	?
16/06/2017	901999	?
23/06/2017	901999	?

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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:

Class. Date	Reg.No	Course
12/05/2017	123456	Maths
19/05/2017	123456	Maths
30/05/2017	123456	Geography
02/06/2017	901999	Science
09/06/2017	901999	Science
16/06/2017	901999	History
23/06/2017	901999	History

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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))