Determining value to return based on date

[DashboardNovice]

In the attached spreadsheet I want to do the following: If a person's timesheet date is 06.15.2014 or before, then Project 1 should appear in column E. If a person's timesheet date is 06.16.2014 or later, then Project 2 should appear in column E.

So the formula would go in column E. My lookup values would be the name in column B and the timesheet date in column C.

The lookup table would be in H3:J14.

So for each row in column E, I need to take into account the person's name and timesheet date.

Notice that in the lookup table, some people are on Project 1 up to 06.15.2014 and some up to 07.13.2014. Then some people start the next project (2 or 3) on either 06.16.2014 or 07.14.2014.

I'm just not sure how to best set up the dates in the lookup table. For any given person the first instance of date for Project 1 is on or BEFORE. The second instance of date for Project 2 or 3 is on or AFTER.

Rows 17 and 23 show a diagram of the two possibilities.

 

[azumi]

I dont check the results, maybe something like this? Using Array Formulas (You Need to Press CTRL-SHIFT-ENTER button together, not ENTER alone), pls see the file attach

Cheers

 

[DashboardNovice]

I dont check the results, maybe something like this? Using Array Formulas (You Need to Press CTRL-SHIFT-ENTER button together, not ENTER alone), pls see the file attach

Cheers

Thank you for your reply and for taking the time to look into this. The first instance of Jim and Kathy should have Project 1. For Jim Davis, his timesheet date is 05.02 which is on or before 06.15 therefore he should have Project 1.

Kathy has a timesheet date of 07.12 which is on or before 07.13, so she should be Project 1.

This is where I am running into confusion about the best way to set up the date criteria in column I.