Get closest value for nearest date

[niko1823]

From the below.
worksheet1 : In worksheet1 "duty" column. I need to find out what duty "worker" last had during work_order_date. Bill on work_order_date 08/01/2017 & 08/05/2017 should report back as "welder" because he was not yet a manager until 8/15/2017. worksheet1 Bill work_order_date 09/20/2017 "duty" column should be "manager" because he was last reported as manager in worksheet2 on 8/15. he was not yet an owner until 10/20.



[TABLE="width: 500"]
<tbody>[TR]
[TD]worksheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]worksheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]worker[/TD]
[TD]work_order_date[/TD]
[TD]duty[/TD]
[TD][/TD]
[TD]worker[/TD]
[TD]employment_date[/TD]
[TD]duty[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]08/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]bill[/TD]
[TD]07/30/2017[/TD]
[TD]welder[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]08/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]bill[/TD]
[TD]08/15/2017[/TD]
[TD]manager[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]09/20/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]bill[/TD]
[TD]10/20/2017[/TD]
[TD]owner[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jan[/TD]
[TD]10/31/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]jan[/TD]
[TD]11/05/2017[/TD]
[TD]client[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. See if this works for you:

=IFERROR(LOOKUP(2,1/((Sheet2!$A$2:$A$5=A2)*(Sheet2!$B$2:$B$5<=B2)),Sheet2!$C$2:$C$5),"not employed")

 

[deverhart]

I just did a vlookup for the information regarding Bill in the duty column : =VLOOKUP(B3,Sheet2!$C$3:$D$7,2,1) so looking up by date but with a TRUE Statement at the end you will get the last row where the range value is <= the lookup value so it came out correctly. The reason i started my dates one column over though is because of the other employee Jan. if you want to differentiate them you can concatenate worksheet two in column A so =A2&B2 which just combines their name and date then you can do =VLOOKUP(A3&B3,Sheet2!$A$3:$D$7,4,1). The only issue here is that on worksheet 2 the only data you have for Jan is after her work order date of 10/31 so it will default to just the closest date which will label her as owner since it was before 10/31. So the workers would need a label prior to the worksheet 1 date. Hope that makes sense.

 

[niko1823]

works great! thank you very much.

 

[niko1823]

=IFERROR(LOOKUP(2,1/((Sheet2!$A$2:$A$5=A2)*(Sheet2!$B$2:$B$5<=B2)),Sheet2!$C$2:$C$5),"not employed")

worked great just needed to sort both workbooks by date (oldest to newest) or else it wouldn't bring back the correct duty from worksheet2.