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

1. 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.

2. Re: Array formula to match value to row and pull row header (multiple occurrences of value

L3=IFERROR(INDEX(\$A\$3:\$A\$12,SMALL(IF(\$B\$3:\$F\$12=\$K3,ROW(\$A\$3:\$A\$12)-ROW(\$A\$3)+1),COLUMNS(\$L\$3:L3))),"")

Control+shift+enter

Copy across and down

3. Re: Array formula to match value to row and pull row header (multiple occurrences of value

a very similar approach using AGGREGATE, non-Array

Formula:
`Please Login or Register  to view this content.`

4. Re: Array formula to match value to row and pull row header (multiple occurrences of value

Another alternative using Power Query (Get & Transform), just for fun. Formula method is faster in this case.

``Please Login or Register  to view this content.``

5. Re: Array formula to match value to row and pull row header (multiple occurrences of value

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

6. Re: Array formula to match value to row and pull row header (multiple occurrences of value

Thanks @ CK76

Sorry for my English

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

Now I understand why

Thanks @ CK76

7. Re: Array formula to match value to row and pull row header (multiple occurrences of value

@CARACALLA

Most of the steps are done using GUI, so no need to manually write M code.

Only place some editing of M code is needed is below part.
``Please Login or Register  to view this content.``
Since Table.Pivot operation does not offer Text.Combine as option. You would use Count(Non blank) option. Then replace the last argument with Text.Combine.

Get & Transform and Power Pivot are best addition to Excel in my opinion. It allows very flexible and quick modeling of data for use in discovery, analysis, and reporting (i.e. self-service BI).

There are currently 1 users browsing this thread. (0 members and 1 guests)