Thread: 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.

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.