Linking two data sets - assistance requested

[PNWilson]

Greetings fellow pivot table practitioners,
I'm trying to link two sets of data. Samples are listed below. I'd like to figure out a formula that populates the "Status" and "Day Status Began" in the top table based on the data in the second table.

EX: On 21 Jan 2015, Scott submitted a report. Per the second table, on that date, he was in a "SLEEP" status, and that status had begun on 20 Jan 2015. Likewise, Tammy submitted a report on 26 Jan 2015, and per the second table, she was in a "WORK" status at the time and had been since 19 Jan 2015.

I think I need a Lookup, but I can't figure it out. Any help would be much appreciated.

Scott

Name	Report Date	Status	Day Status Began
Scott	21 Jan 2015
Tammy	21 Jan 2015

<tbody>
</tbody>

Name	Status	Day Status Began	Day Status Ended
Scott	WORK	1 Jan 2015	19 Jan 2015
Scott	SLEEP	20 Jan 2015	23 Jan 2015
Tammy	SLEEP	3 Jan 2015	18 Jan 2015
Tammy	WORK	19 Jan 2015	27 Jan 2015

<tbody>
</tbody>

 

[PNWilson]

Sorry, made a slight mistake in the original post. In the example, the top sample table should read that Tammy submitted a report on 26 Jan 2015 (to match the written portion).