Vlookup mathing

[niko1823]

[TABLE="width: 500"]
<tbody>[TR]
[TD]dos
[/TD]
[TD]id
[/TD]
[TD]dep?
[/TD]
[TD]id
[/TD]
[TD]begin
[/TD]
[TD]end
[/TD]
[TD]dept
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/22/2018
[/TD]
[TD]1234
[/TD]
[TD][/TD]
[TD]1234
[/TD]
[TD]4/13/2018
[/TD]
[TD]12/31/9999
[/TD]
[TD]205
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/6/2018
[/TD]
[TD]1234
[/TD]
[TD][/TD]
[TD]1234
[/TD]
[TD]3/1/2018
[/TD]
[TD]4/12/2018
[/TD]
[TD]105
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/25/2018
[/TD]
[TD]1222
[/TD]
[TD][/TD]
[TD]1222
[/TD]
[TD]1/31/2018
[/TD]
[TD]7/31/2017
[/TD]
[TD]106
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have the following. I'm trying to get the dept? a member was in on the dos (date of service). vlookup on its own will not work because it will always return the first match of the id.
in the above example id 1234 dos 3/22/2018 was in dept 105 because 3/22 falls within the begin range 3/1-4/12.

 

[niko1823]

Any help would be much appreciated

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G
1	dos	id	dep?	id	begin	end	dept
2	22/03/2018	1234	105	1234	13/04/2018	31/12/9999	205
3	04/06/2018	1234	205	1234	01/03/2018	12/04/2018	105
4	25/03/2018	1222	106	1222	31/01/2018	31/07/2018	106
Sheet2

Cell Formulas
Range	Formula
C2	{=INDEX($G$2:$G$4,MATCH(1,(A2>=E$2:E$4)*(A2<=F$2:F$4)*(B2=D$2:D$4),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[niko1823]

Thank you very much! this does exactly what I need

 

[Fluff]

You're welcome