+ Reply to Thread
Results 1 to 3 of 3

Retrieve Multiple Values Horizontally Using Partial Match

  1. #1
    Registered User
    Join Date
    12-18-2017
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    2

    Retrieve Multiple Values Horizontally Using Partial Match

    Hi All,

    I know this is a fairly common topic, but I'm still having trouble.

    My table:
    Column A) Account ID
    Column B) Account Name
    Column M) Parent ID

    Using partial account name, I need to retrieve all values in Column M that match. I'd like the output to populate horizontally in separate columns.

    I was using =VLOOKUP(PARTIALNAME&"*",$B$2:$M$42000,12,FALSE), but this does not work when there are multiple values in M or if there is a single value not listed first in the lookup.

    I've also tried =IF(COLUMN()-2<=COUNTIF($B$2:$B$42000,PARTIALNAME&"*"),INDEX($M$2:$M$42000,MATCH(PARTIALNAME&"*",$B$2:$B$42000,0)+COLUMN()-3),"")

    Thank you in advance.

  2. #2
    Registered User
    Join Date
    12-18-2017
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    2

    Re: Retrieve Multiple Values Horizontally Using Partial Match

    B1: Account Name
    B2: Alex Corp
    B3: Alexander's Foods
    B4: Alex & Co
    B5: Alex Store
    B6: Brian's Shop
    B7: Brian Place
    M1: Parent ID
    M2: No data
    M3: 40001
    M4: 40002
    M5: No data
    M6: 3321
    M7: No data
    Z300: Alex

    I want to reference Z300 and return 40001 and 40002 horizontally.

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    5,716

    Re: Retrieve Multiple Values Horizontally Using Partial Match

    Hello M_Leigh and Welcome to Excel Forum.
    Using the set up given an array entered formula that would yield the expected results (G2:I2 of attached file) is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The attached file models the setup a bit differently for the purpose of displaying all the parts at one time.
    In the future you will likely get quicker results if you upload a copy of your spreadsheet by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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