Results 1 to 6 of 6

Thread: Return multiple values in one cell from VLOOKUP
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return multiple values in one cell from VLOOKUP

    Hi,

    I am using this formula

    =CHOOSE(SUMPRODUCT(--(B11=A1:A8)),
    VLOOKUP(B11,A1:B8,2,0),
    VLOOKUP(B11,A1:B8,2,0) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+1),
    VLOOKUP(B11,A1:B8,2,0) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+1) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+2))


    to return more than one value in a cell from VLOOKUP in relation to this thread

    https://www.mrexcel.com/forum/excel-...same-cell.html


    It works fine except that some of the cells that are being searched have more than one value themselves so I need to use a "contains" expression rather than just equals.

    I have tried a wildcard "*"& B11 &"*" which returns a result if the searched cell "contains" the value of the refernce cell but I seem to have lost the abilty to return more than one value in a cell. Am I putting the wildcrd in the wrong place?

    Many thanks

    Phil

  2. #2
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return multiple values in one cell from VLOOKUP

    I have added FALSE instead of "0" value as shown here

    =CHOOSE(SUMPRODUCT(--(B11=A1:A8)),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+2))

    which gives an odd result woth some vakues returned correctly and some not. As you can see below the result should be red, blue, green but instead I only get two colours returned.


    A1, A2 Red
    A1 Blue Red, Green
    A1 Green
    A2 Purple
    A2 Orange
    A3 Yellow
    A4 Black
    A5 White
    Lookup
    a1

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,170
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Return multiple values in one cell from VLOOKUP

    Hi & welcome to MrExcel.
    Try
    =CHOOSE(SUMPRODUCT(--(ISNUMBER(SEARCH(B11,A1:A8)))),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+2))
    Last edited by Fluff; Oct 14th, 2019 at 03:51 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,170
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Return multiple values in one cell from VLOOKUP

    If you have the TEXTJOIN function you could use
    =TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(B11,A1:A8)),B1:B8,""))

    Confirmed with Ctrl Shift Enter, not just Enter.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return multiple values in one cell from VLOOKUP

    Quote Originally Posted by Fluff View Post
    Hi & welcome to MrExcel.
    Try
    =CHOOSE(SUMPRODUCT(--(ISNUMBER(SEARCH(B11,A1:A8)))),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1),
    VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+2))
    HI Fluff,

    Thank you. I now get Red, Green, Purple returned

    Cheers

    Phil

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,170
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Return multiple values in one cell from VLOOKUP

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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