+ Reply to Thread
Results 1 to 7 of 7

Vlookup with multiple results comma seperated in cell

  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    Monnickendam, The Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    4

    Vlookup with multiple results comma seperated in cell

    Hi guys,

    I have a problem with a spreadsheet. I'm not new to excel as I have enough experience, but I think this problem is a little bit too big for me to solve. First let me introduce the problem, i have the following situation:


    Column A | Column B
    Henry First | 1212121
    Derek | 3424243
    Bas | 8969869
    Henry Second | 7698698
    Henry Third | 7877868

    What I need is the following I want to return the value of column B for every row which contains "Henry" in column A, and these values have to be seperated with a comma.
    So it needs to be a sort of VLookup.

    It should return 1212121,7698698,7877868 in a cell when I look for Henry.

    I'm not familiar with Visual Basic and macros, and I'm actually not using excel as I'm using open office calc.

    I hope someone can help me.

    Kind regards,

    Joey

  2. #2
    Registered User
    Join Date
    04-14-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: Vlookup with multiple results comma seperated in cell

    Would it be sufficient to type "Henry" into say D1 and have the results "1212121, 7698698, 7877868" show up in cells D2:D4 respectively? I can probably set that up for you, if that works for you.
    If you found this helpful, click the star

  3. #3
    Registered User
    Join Date
    04-18-2013
    Location
    Monnickendam, The Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Vlookup with multiple results comma seperated in cell

    Unfortunately that is not enough, it should really come in one cell. This is only a small part of a very large CSV file.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup with multiple results comma seperated in cell

    Does OO work with VBA?

    If you were using Excel you would need some form of VBA to do this.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    04-18-2013
    Location
    Monnickendam, The Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Vlookup with multiple results comma seperated in cell

    I thought so yes. I also found this code for Excel:

    Please Login or Register  to view this content.
    At this page: http://bals-brain.com/Multi_vlookup.htm

    However, I thought I was pretty experienced with Excel, but it seems I'm a real noob. I really have no idea how to implement this.

    Joey

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup with multiple results comma seperated in cell

    That function won't work with your data because you have more text besides the lookup value in some of the cells. That function only works on "exact" matches. If the cells only contained the lookup value Henry, then it would work.

    Does OO support array formulas?

    Here's some code I have that can be used to do conditional concatenation but it has to be entered as an array formula.

    Please Login or Register  to view this content.
    I don't know how to set this up in OO but here's how you'd do it in Excel...

    With your file open...

    Right click any sheet tab

    Select View Code

    CTRL R (this will open the Project Explorer pane on the right side of the window)

    Find your file name in the list of files presented. It will appear as: VBAProject(your file name here)

    Select your file name then right click, select Insert>Module

    Copy the code above and paste it into the right side of the window that opens

    Close the VBE and return to Excel: ALT F11

    Now, to use this function...

    If this is your data in the range A2:B6...

    Henry First........123
    Derek...............456
    Bas..................789
    Henry Second....112
    Henry Third.......345

    Enter the lookup value in a cell: D2 = Henry

    Enter this array formula** in E2:

    =SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(SEARCH(D2,A2:A6))," "&B2:B6,"")))," ",", ")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Here's an Excel file that demonstates this:

    ledworld.xls
    Last edited by Tony Valko; 04-18-2013 at 03:34 PM.

  7. #7
    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: Vlookup with multiple results comma seperated in cell

    The attached file shows how you can do it with formulae. I've used cell D2 (yellow) to allow you to enter your name, then in C2 I've used this formula:

    =IF(ISNUMBER(SEARCH($D$2,A2)),MAX(C$1:C1)+1,"-")

    which can be copied down even beyond your data (as the hyphens indicate). C1 should be left empty, or you could put 0 in there or some text. Then in E2 I have this formula:

    =IFERROR(INDEX(B:B,MATCH(1,C:C,0)),"")&IFERROR(","&INDEX(B:B,MATCH(2,C:C,0)),"")&IFERROR(","&INDEX(B:B,MATCH(3,C:C,0)),"")&IFERROR(","&INDEX(B:B,MATCH(4,C:C,0)),"")

    which will give you up to 4 numbers where column A contains the name in D2. If you need to see more, then you can see that the last 3 terms in the formula are almost identical - the only difference is the number in the MATCH function. So, you could highlight this part within the formula bar:

    &IFERROR(","&INDEX(B:B,MATCH(4,C:C,0)),"")

    then CTRL-C, then click to the end of the formula and CTRL-V a few times, and then change the 4 to 5, 6, 7 etc in subsequent terms. You haven't indicated how many repeats you are likely to have, but if it is a few dozen or more then this would be a bit impractical, and you would need to use VBA.

    Hope this helps.

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

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