+ Reply to Thread
Results 1 to 6 of 6

Lookup and return multiple results

  1. #1
    Forum Contributor
    Join Date
    06-26-2014
    Location
    Houston
    MS-Off Ver
    365
    Posts
    196

    Lookup and return multiple results

    Hi!

    I am struggling to determine a formula to return multiple results based on a lookup. The only examples I can find are not quite what I'm looking for. Two columns of data as the source data. I would try and explain what I'm looking for, but the sheet I have attached may give a better description. Source table on the left, desired result on the right. Let me know if I can help describe it.

    Thank you!!!
    Attached Files Attached Files

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Lookup and return multiple results

    Here you can find 2 options, most likely you will use the UDF:https://www.get-digital-help.com/201...into-one-cell/
    Click the * to say thanks.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Lookup and return multiple results

    This can be done with helper column, is it ok for you
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Lookup and return multiple results

    I am not sure that have you got TEXTJOIN so use this:

    Please Login or Register  to view this content.
    paste this VBA into module and then enjoy like below:

    A
    B
    C
    D
    2
    APACHE06
    1
    APACHE06 1,2
    3
    APACHE06
    1
    APACHE07 100389-1,100389-2,100389-3
    4
    APACHE06
    1
    APACHE08 47771-1,47771-2


    Excel 2016 (Windows) 32 bit
    C
    D
    1
    WP ID Tank
    2
    APACHE06 =TEXTJOINsub(IF($A$2:$A$70=C2,$B$2:$B$70,""),",",1)
    3
    APACHE07 =TEXTJOINsub(IF($A$2:$A$70=C3,$B$2:$B$70,""),",",1)
    Sheet: Sheet 1

    Formula have to be accept with Ctrl+Shift+Enter (not just Enter) as is array formula.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup and return multiple results

    As you can see, there are a number of ways to achieve this. In my suggestion I use two helper columns (C and D), with this formula in C2:

    =IF(A2="","-",IF(COUNTIF(A$2:A2,A2)=1,MAX(C$1:C1)+1,"-"))

    and this one in D2:

    =IF(A2="","-",IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,A2&"_"&COUNTIF(D$1:D1,A2&"*")+1,"-"))

    These two formulae should be copied down to the bottom of your data or beyond, as can be seen by the hyphens.

    To get the data in the form you require, you can then use this formula in G2:

    =IFERROR(INDEX(A:A,MATCH(ROWS($1:1),C:C,0)),"")

    and this one in H2:

    =IF(G2="","",INDEX(B:B,MATCH(G2&"_1",D:D,0)) & IFERROR(", "&INDEX(B:B,MATCH(G2&"_2",D:D,0)),"") & IFERROR(", "&INDEX(B:B,MATCH(G2&"_3",D:D,0)),"") & IFERROR(", "&INDEX(B:B,MATCH(G2&"_4",D:D,0)),"") & IFERROR(", "&INDEX(B:B,MATCH(G2&"_5",D:D,0)),""))

    This one might look a bit daunting, but if I put manual line-breaks in it you can see it more clearly:

    =IF(G2="","",INDEX(B:B,MATCH(G2&"_1",D:D,0))
    & IFERROR(", "&INDEX(B:B,MATCH(G2&"_2",D:D,0)),"")
    & IFERROR(", "&INDEX(B:B,MATCH(G2&"_3",D:D,0)),"")
    & IFERROR(", "&INDEX(B:B,MATCH(G2&"_4",D:D,0)),"")
    & IFERROR(", "&INDEX(B:B,MATCH(G2&"_5",D:D,0)),""))

    This will return up to 5 items from column B (the most you have in your example data is 4), but it is easy to extend it to more by adding extra terms inside the final bracket and changing the coloured number as appropriate.

    The two formula in G2:H2 can be copied down until you start to get blanks. The attached file shows this in action, and I have moved your Desired results to the right so that you can compare with the formulae-derived results directly. All the formulae are shown in blue.

    Hope this helps.

    Pete
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Lookup and return multiple results

    Please try at
    C2
    =IF(B2=B1,"",B2&IFERROR(", "&INDEX(C3:C$70,MATCH(1,INDEX((A3:A$70=A2)*(C3:C$70<>""),),)),""))

    H2
    =VLOOKUP(G2,$A$2:$C$70,3,0)
    Attached Files Attached Files

+ 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. [SOLVED] Lookup to match multiple criteria and return multiple results excluding duplicates.
    By kris3012 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-13-2018, 11:06 AM
  2. Lookup a Value and Return Multiple Results
    By tigeravatar in forum Tips and Tutorials
    Replies: 13
    Last Post: 04-08-2016, 04:44 PM
  3. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  4. Lookup a Value and Return Multiple Results
    By sgtkikass in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-06-2014, 10:06 PM
  5. [SOLVED] Lookup to return multiple results - without array
    By ZeDoctor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2012, 07:42 AM
  6. Lookup & return Multiple Results
    By benishiryo in forum Excel Tips
    Replies: 2
    Last Post: 07-11-2012, 10:11 AM
  7. Return Multiple Results with Lookup
    By Josh O. in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2005, 05:06 PM

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