Find corresponding date range based on single date and ID number

[Mate70]

Hi, hope that you can help me with this one.
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.

I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8

Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014

What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1

In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014

Thanks!

[RudiS]

Excel will never know what dates (from set 2) to add to what records (in set 1), because the employee number is not unique and even if you concatenate employee number with (date/start date), there is no matches because the start date in set 2 is different to the date in set 1.

There is no logical combinations in the data sample you provided.

[adyteo]

are the employee numbers unique in each of the sheets?

[Mate70]

The first sheet contains per employee number the hours per day
The second sheet contains the diferent periods for that employee
In the example I've used just one employee (12345), the same is used in both sheets

[AZ-XL]

Can you upload sample file and desired results.

[RudiS]

I doubt this is correct as I still don't understand the set1 vs. set2 data.

Lookup.xlsx

[Mate70]

The sample file contains a bit more.
And please e aware of the European date format :-)

Tnx

[Mate70]

What it "basicly" has to do is look for the periods of that employee and finding the previous date (based on the date in sheet/set 1) that is available, and show it as 1st day.
For the end day it should find te next available day in the slection of periods for that employee.

[RudiS]

See the result...
It finds matches, but only for a few...

sample_upload.xlsx

[Mate70]

yeah, I came that far. Thanks though.
I finds it when the date in the first sheet is found in the second.
But if it can't find it, it has to find the first date before that in the available selection of the employee

[Mate70]

I found a other thread with a formula that can return a previous date based on a given name and date.
I can't seem to understand it and use it on my own.

The used formula is:
=INDEX(PURCHASE!$A$1:$D$42,MATCH($A2,IF((PURCHASE!$B$1:$B$42=SALE!$B2),PURCHASE!$A$1:$A$42),1),{1,2,4})

[RudiS]

I'll look at that formula...but on my way out now...

I came closer with this, but it is not a good solution and I wouldn't recommend using it!!
=IFERROR(VLOOKUP(A2&B2,Periods!$A$2:$E$5,3,0),D1)

[RudiS]

A modified solution seems to do the job...
See attached...

sample_upload solution.xlsx

[romperstomper]

You can also use a LOOKUP formula:

=IFERROR(LOOKUP(2,1/(Periods!$A$2:$A$200=$A2)/(Periods!$B$2:$B$200<=$B2)/(Periods!$C$2:$C$200>=$B2),Periods!B$2:B$200),"")

Adjust the ranges to suit.