Index/Match on multiple criteria and between date

[snake]

I've been searching all morning for a solution and although I think an Index Match is the answer I'm getting stuck with the formula and how best to apply it.

I have two tables one table is a job summary table that has a JobID with an employee name and a start/end date.

The second table is a list of jobs were an employee was, and I'm trying to have the Job ID added to that table.

The formula should return the jobID for where the Job date is between the start/end date and for that employee name.

For example for the first row in Table two, the JobID should be job9897 because jane worked on that project on Jan 1.

First is Index/Match the best option, and if so, how would I develop the formula?

Table 1

JobID	EmployeeName	Start Date	End Date
job9897	Jane	1-Jan-19​	21-Jan-19​
job4934	Jane	31-Jan-19​	18-Feb-19​
job3813	Mike	1-Mar-19​	1-Apr-19​
job7075	Mike	12-Apr-19​	30-Apr-19​
job6153	Josh	1-Jan-19​	19-Jan-19​
job4076	Josh	1-Apr-19​	29-Apr-19​

Table 2

JobID	Vessel Name	Job Date
	Jane	1-Jan-19​
	Jane	3-Jan-19​
	Jane	4-Jan-19​
	Jane	5-Jan-19​
	Jane	1-Feb-19​
	Jane	4-Feb-19​
	Jane	5-Feb-19​
	Jane	6-Feb-19​
	Jane	1-Mar-19​
	Jane	2-Mar-19​
	Mike	2-Mar-19​
	Mike	3-Mar-19​
	Mike	4-Mar-19​
	Mike	5-Mar-19​
	Mike	29-Apr-19​
	Mike	30-Apr-19​
	Mike	1-May-19​
	Mike	2-May-19​
	Josh	6-Jan-19​
	Josh	7-Jan-19​
	Josh	8-Jan-19​
	Josh	9-Jan-19​
	Josh	10-Jan-19​

 

[steve the fish]

Make sure your dates are true dates and not text then try this in A11:

=LOOKUP(2,1/(($B$2:$B$7=B11)*($C$2:$C$7<=C11)*($D$2:$D$7>=C11)),$A$2:$A$7)

with your tables in A1:D7 and A10:C33.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E	F	G	H	I
1	JobID	EmployeeName	Start Date	End Date			JobID	Vessel Name	Job Date
2	job9897	Jane	01/01/2019	21/01/2019			job9897	Jane	01/01/2019
3	job4934	Jane	31/01/2019	18/02/2019			job9897	Jane	03/01/2019
4	job3813	Mike	01/03/2019	01/04/2019			job9897	Jane	04/01/2019
5	job7075	Mike	12/04/2019	30/04/2019			job9897	Jane	05/01/2019
6	job6153	Josh	01/01/2019	19/01/2019			job4934	Jane	01/02/2019
7	job4076	Josh	01/04/2019	29/04/2019			job4934	Jane	04/02/2019
8							job4934	Jane	05/02/2019
9							job4934	Jane	06/02/2019
10								Jane	01/03/2019
11								Jane	02/03/2019
12							job3813	Mike	02/03/2019
13							job3813	Mike	03/03/2019
14							job3813	Mike	04/03/2019
15							job3813	Mike	05/03/2019
16							job7075	Mike	29/04/2019
17							job7075	Mike	30/04/2019
18								Mike	01/05/2019
19								Mike	02/05/2019
20							job6153	Josh	06/01/2019
21							job6153	Josh	07/01/2019
22							job6153	Josh	08/01/2019
23							job6153	Josh	09/01/2019
24							job6153	Josh	10/01/2019
List

Cell Formulas
Range	Formula
G2:G24	G2	=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$2)+1)/(($B$2:$B$7=H2)*($C$2:$C$7<=I2)*($D$2:$D$7>=I2)),1)),"")

 

[steve the fish]

Or as an index match this would work:

=INDEX($A$2:$A$7,MATCH(1,INDEX(($B$2:$B$7=B11)*($C$2:$C$7<=C11)*($D$2:$D$7>=C11),0),0))

 

[snake]

thank you all for your advice, all three options work perfectly - I tested all three on the full dataset and all three returned the right answer.

 

[Fluff]

Glad we could help & thanks for the feedback.