Array formula to match value to row and pull row header (multiple occurrences of value)

I have received data from a survey where companies were asked to nominate up to 5 other companies. I am trying to list the nominated companies and who nominated them. One nominee can be in multiple rows or columns. I have normalized the data and pulled the nominees into a separate column. I am trying to create a formula to search the original list for the nominee, find a match in the row and pull the company from the row header into the new list. I was able to pull a list using Index/Match but if the Nominee was in the column multiple times I only got the first value. In the example Nominee "W" should show that companies "3", "7", and "5" nominated them.

Thanks in advance

a very similar approach using AGGREGATE, non-Array

Formula:

Please Login or Register  to view this content.

Thanks. I haven't used Row or Column and wasn't aware of the Aggregate function.

Thanks @ CK76

Sorry for my English

I have tried with Power Query but I have not succeeded.



Now I understand why



Thanks @ CK76