+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    06-18-2020
    Location
    Georgia
    MS-Off Ver
    Office 365
    Posts
    2

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,309

    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
    Last edited by CARACALLA; 06-18-2020 at 11:18 AM.

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,575

    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: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    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.
    Attached Files Attached Files
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  5. #5
    Registered User
    Join Date
    06-18-2020
    Location
    Georgia
    MS-Off Ver
    Office 365
    Posts
    2

    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. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,309

    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. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Array Pull With Transpose Between Two Dates? [Tricky Index/Match Formula]
    By larryg003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2017, 10:41 AM
  2. Replies: 2
    Last Post: 07-03-2017, 10:09 AM
  3. Replies: 4
    Last Post: 06-30-2015, 03:49 PM
  4. Issue using Index/Match to pull multiple occurrences of "Match" criteria
    By aridfriedman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2014, 11:38 AM
  5. Replies: 6
    Last Post: 04-12-2013, 05:50 AM
  6. Replies: 10
    Last Post: 12-18-2012, 07:59 AM
  7. Replies: 13
    Last Post: 11-25-2011, 04:57 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1