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

[clobus]

I 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

2 Project 1 [time spent]

3 Project 2 [time spent]

4 Project 3 [time spent]

5 Employee B

6 Project 2 [time spent]

7 Project 4 [time spent]

8 Project 5 [time spent]

9 Employee C

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!

 

[ACK93]

Hello. I'm finding a bunch of helpful posts on this site. I recently viewed one that appears similar to what you may be trying to do. Try searching on the site for "Getting the 2nd matching value from a list using VLOOKUP formula". Perhaps it may help you?

 

[clobus]

Thank you for this suggestion. I searched the site and found the article you are referring to. So, I tried it out, but got a "1" after everything, no matter if it was listed before or not. Any other suggestions?

 

[NARAYANK991]

Hi ,


You have given the requirements of your report ; can you also indicate how the raw data , from which your report should be created , is organized ?


Can you copy + paste about 10 rows from this raw data ?


Narayan

 

[clobus]

Sure, Narayan, and thank you, all, for continuing to provide me feedback/help. I greatly appreciate it.


Below is how my report is being given to me for one month (I changed the names to protect the innocent). Column A is "Person/Project" and Column B is "Time (decimal).


Person/Project Time (decimal)

Dan Rather 176.314

1companyx - Nonbillable 123.941

1companyx - work - work 2.193

company y - work 1.039

company z - Services 6.398

company a - Corp. - Services 41.663

company b - work 1.081

Chuck Norris 0.055

1companyx - Nonbillable 0.055

company y - work 1.039

company z - Services 6.398

Homer Simpson 152.500

1companyx - Nonbillable 123.941

1companyx - work - work 2.193

company y - work 1.039

company z - Services 6.398


If you copied/pasted this into Excel, you would see that all of the names/projects are in the same column, which makes it hard to break out the projects for the various people.


The way I want to display the data is to show all of the time spent on each project for each month for each person. For each person, I want to list months vertically and then projects horizontally with the time spent on each project in the grid, like below:


Person One

column A, column B, column C....

mth 1, time, time, time

mth 2, time, time, time


Person Two

column A, column B, column C....

mth 1, time, time, time

mth 2, time, time, time


The amount of projects and people vary by month, so I need a dynamic way of looking up only the projects that apply to only the people in the report.


Thank you for your help.

 

[NARAYANK991]

Hi ,


From what data you have posted , it should be possible to generate the matrix report ; but how do you distinguish one month's data from the next ?


Secondly , against each person , there is a numeric time value shown ; what is this ? How does it relate to the time values shown against each project ?


Narayan

 

[clobus]

I receive a separate report for each month and the time values by project, by person is the data I am attempting to capture so that I can build a cumulative graph for the entire year. So, I will get data similar to what I posted in January, for instance, but all of the employees and projects for the following months, February, will be different. Person One may or may not be associated with the same projects or not even be listed at all. For that reason, I need a dynamic equation that searches the entire column for Person One's name and then all of the projects beneath him/her and then displays the time values for those projects some where else so I can graph how much time that person is associated with each project.


Chris