Excel Formula Help

Got a tough one here I'm trying to figure out.

A B C D
Work Area Name Work Area Signout Date Completed Work Area #
New York Bob 4/14/2010 1
Washington Fred 4/14/2010 1
Chicago Mike 4/14/2010 1
Los Angeles Jerry 4/14/2010 1
Sheboygan Fred 4/14/2010 2
Boise Bob 4/15/2010 2
Cincinnati Mike 4/15/2010
Springfield

Using Office 2007
Using columns B and C of the data I want to be able to generate the value in the last column to automatically fill in which work area this is that they are performing the work on where, for example, Mike has completed his first work area on 4/14/2010 and has just finished his second work area on 4/15/2010. I want the formula to first of all generate that Chicago was his first work area and then that Cincinnati is his second work area that he has completed and be able to do the same for all others on the table. There is also a high chance that one might finish 2 work areas on the same day so still need to be able to factor that in as well. Additionally there is no particular order in which the data in column A or B would be entered, it's just whoever happens to check out the next work area.
Just trying to make it easier to populate this column without having to spend time looking through each person's name manually to determine which work area it is that they have finished.
If it is not possible to create a formula like this then so be it, but i'm really interested to see if one can be made so this would work.

Thanks