Help needed! INDEX/MATCH, Ignore Blanks in an array

Hello All,

I have searched online for this solution but I cant seem to get any formulas to work for my example. I tried everything but sill cant manage to figure out how to use INDEX / MATCH for my specific case.

The document is based on two tables. In the first one there is a table where the projects can be found (as an example, project A and project B). If a certain project deals with a certain activity, the area is highlighted (Example, project A works with Standards and Laboratory Support, while project B also works with rural development). The table is formatted in this way in order to be able to filter between the projects based on activities.

My question would be how to do, on the basis of this data, what appears in Sheet2 (Table 2). I would like to create a summary for each project where the activities are mentioned horizontally. Here lies my difficulty. I look for a method to be able to enumerate in each project the activities, skipping the blank cells and that are presented in a horizontal way.

I've tried to use INDEX / MATCH / Horizontal Array / but I haven't been lucky, I don't know if because of lack of knowledge or because of the format. I hope someone has experience with this format and can help me.

I have attached a sample workbook to demonstrate what I mean

Kind regards

Hello JavierEM. Welcome to the forum.

In the attached please find:

• copy of 'Sheet2' called 'Sheet2 (2)'
• The headers Projects# changed to Project# so they match in the MATCH function.
• This formula in A2 filled down and across until you get blanks.
  Formula:

  Please Login or Register  to view this content.