Multi-Compare Columns to fetch an output

[excel1404]

Hello Gurus,

Need your help with the following:

Table 1: Has the following values

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Employee[/TD]
[TD]Value[/TD]
[TD]Numbering[/TD]
[/TR]
[TR]
[TD]2019-05-15[/TD]
[TD]12345[/TD]
[TD]10[/TD]
[TD]Fill this row[/TD]
[/TR]
[TR]
[TD]2019-01-04[/TD]
[TD]11111[/TD]
[TD]1[/TD]
[TD]Fill this row[/TD]
[/TR]
</tbody>[/TABLE]


Table 2: has the following values with a start date and end date as a range and 2019-05-15 could be in one of the range for this employee

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Value[/TD]
[TD]Units[/TD]
[TD]Numbering[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]2019-01-01[/TD]
[TD]2019-01-05[/TD]
[TD]11[/TD]
[TD]days[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]2019-01-01[/TD]
[TD]2019-04-05[/TD]
[TD]72[/TD]
[TD]days[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]12345[/TD]
[TD]2019-05-10[/TD]
[TD]2019-05-25[/TD]
[TD]13[/TD]
[TD]days[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My aim is to get the table 1 filled with Numbering from Table 2 based on the following checks:
1. Check the Employee number from Table 1 equals employee number in Table 2.
2. When the match is found, check the corresponding date falls in between the start date and end date of table 2 (including these dates).
3. When this match is found, then choose the number in table 2, and enter that in numbering column in Table 1.

Thank You.

Excel1404

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:71.29px;" /><col style="width:65.58px;" /><col style="width:40.87px;" /><col style="width:73.19px;" /><col style="width:76.04px;" /><col style="width:65.58px;" /><col style="width:71.29px;" /><col style="width:71.29px;" /><col style="width:40.87px;" /><col style="width:37.07px;" /><col style="width:73.19px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Date</td><td >Employee</td><td >Value</td><td >Numbering</td><td > </td><td >Employee</td><td >Start Date</td><td >End Date</td><td >Value</td><td >Units</td><td >Numbering</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">15/05/2019</td><td style="text-align:right; ">12345</td><td style="text-align:right; ">10</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:right; ">11111</td><td style="text-align:right; ">01/01/2019</td><td style="text-align:right; ">05/01/2019</td><td style="text-align:right; ">11</td><td >days</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">04/01/2019</td><td style="text-align:right; ">11111</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td > </td><td style="text-align:right; ">12345</td><td style="text-align:right; ">01/01/2019</td><td style="text-align:right; ">05/04/2019</td><td style="text-align:right; ">72</td><td >days</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">12345</td><td style="text-align:right; ">10/05/2019</td><td style="text-align:right; ">25/05/2019</td><td style="text-align:right; ">13</td><td >days</td><td style="text-align:right; ">3</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=SUMPRODUCT(($F$2:$F$4=B2)*($G$2:$G$4<=A2)*($H$2:$H$4>=A2)*($K$2:$K$4))</td></tr></table></td></tr></table>