Lookup one value in a range and then lookup another value beneath it

Multiple lookup problem.xlsxI need help writing a formula that looks up multiple referenced in the same column. The report lists an employee's name and then the project(s) he/she is working on beneath him/her in same column. The projects and employees are not constant. One month employee A will be listed first and then the next month Employee B or C is listed first and Employee A is not listed at all. Also, the projects are not constant either. Employee A may work on Projects 1, 2, and 3 in one month and then projects 3, 4, and 5 the next month. This is how the report looks (and changes every month):

A B
1 Employee A [time spent]
2 Project 1 [time spent]
3 Project 2 [time spent]
4 Project 3 [time spent]
5 Employee B [time spent]
6 Project 2 [time spent]
7 Project 4 [time spent]
8 Project 5 [time spent]
9 Employee C [time spent]
10 Project 1 [time spent]
11 Project 5 [time spent]

I have attempted to use INDEX and MATCH functions together as well as IF/AND/MATCH/OFFSET functions together, but that only helps me get the first project beneath the employee. I want separate the employees to show what projects (and for how long) each employee is working on per month.

Thank you for your help!

See attachment.

Same solution as Wher, but sligtly simplified (since you have XL 2007 I've used IFERROR function).

Take 2 - Multiple lookup problem.xlsx

All,

Thank you for your respones, but I am afraid that I still neeed more help. I am attaching another file that is more true to the format of the data. It also explains how I receive the data month by month and how I want to display it cumulatively. I will continue to attempt to use the formulas you already provided to this, but have already spent a couple of hours tyring that and haven't yet figured it out. Thank you for any help you can provide.

Chris

My two-cents worth - I think the pivot table solution is going the right direction. A problem that needs to be overcome is distinguishing between a person and a project, since not all people start with "Person " and not all projects end in a number. Is it possible to have a table of all potential people? Then, a formula would test to see if Column A is found in that table - if so, it is a person, and if not, it is a project.

Also, how are subsequent months handled? Do they get added to the bottom of Columns A and B? If so, I assume the month/year could be indicated beside them, so as to get them in the right columns of the pivot table.