Multi-Compare Columns to fetch an output

excel1404

New Member
Joined
Mar 27, 2013
Messages
19
Hello Gurus,

Need your help with the following:

Table 1: Has the following values
9AYGJjx


9AYGJjx
[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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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>
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top