Using VLookup for another sheet and returning values from multiple rows?

[crybloodwing]

So I have a master list of companies, people, and their title and separate sheets for each company. I want each company sheet to lookup if each person listed for their company is C-Level or above(CEO, President, CFO, etc) and then list all of the C-Level names on that company sheet horizontally.


I started by adding a column to the master sheet that sees if the job title has any of the C-Level words/titles and then to eturn true if they are C-Level, or False if they are not. This worked totally fine.


However, I am stuck on how to move those names over to the company sheet. I am able to move the first one listed for the company using this formula. =IF(ISNUMBER(SEARCH((VLOOKUP(A1,Sheet2!A:C,3,FALSE)), "TRUE")), VLOOKUP(A1,Sheet2!A:C,2,FALSE), 0). However, I do not know how to get the rest of the people that are C-Level.


The company name is in cell A1 for each sheet and I am matching that cell to the company name in the master sheet. How do I make it so it copies over the first C-Level person, then continues down the rows, putting C-Level people in a horizontal list, until it reaches a row that does not match the company name?


I have an example below with stuff I made up because of confidentiality.



Screenshot (7).png

Screenshot (10).png

What I want is a result like this:

Screenshot (11).png

On the actual one however, I have 862 rows on the master sheet and 7 columns.

Thanks!

[José Augusto]

Hi

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[crybloodwing]

Hi

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

I do not need help with the results or anything, that part is working fine. I simply looked up if someone' title contained "CEO, CFO, President, etc" and then put in a cell next to them true or false.

If you look at the pictures, Sheet 2 is like the master list of all companies. Sheet 3 is the specific company sheet. I need a formula that will:

1. Look at A1 in Sheet 1
2. See where on Sheet 2, that name is in the A column.
3. Go over and see if Column C in that row says true or false.
4. If true, copy what is in Column B to Sheet 2, next to the "Executive Management" cell.

This is what I have been able to do with the formula mentioned.

However, what I also want it to do is
5. go back to Sheet 2 and look at the next row that contains the company name in Sheet 3 A1.
6. If Column C is true, copy the name in Column B to the Sheet 3 cell below the previous name.
7. Continue going through the rows until all people who have true next to their name for that company are listed in Sheet 3.

So like in the picture, Sheet 3 is for the White company.

It will look at Sheet 2 and be like
"Here is White.....That says false, don't copy Mark over.
Here is another White. That is true, copy Mary over.
Another White and it is true. Copy Joseph over.
Another White and it is false. Don't copy Evan over.
No more White, so I am done."