Page 1 of 2 12 LastLast
Results 1 to 10 of 20

vlookup with multiple text value return

This is a discussion on vlookup with multiple text value return within the Excel Questions forums, part of the Question Forums category; I'm trying to create a formula that returns with all the text values for all the conditions. So similar to ...

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    Redmond, WA
    Posts
    214

    Default vlookup with multiple text value return

    I'm trying to create a formula that returns with all the text values for all the conditions. So similar to a sumproduct where the formula returns a summed value of ALL the conditions i need the same but for text. Any ideas???

    hello no
    hello no
    hello yes
    goodbye no

    formula would return with (no, no, yes).

  2. #2
    Board Regular
    Join Date
    Dec 2006
    Posts
    511

    Default

    I am having a hard time understanding your example. Can you clarify, and maybe give a better example with more explanation?

  3. #3
    Board Regular Lewiy's Avatar
    Join Date
    Jan 2007
    Location
    Hyrule
    Posts
    4,281

    Default

    I can't think of a sensible formula for this, you would be better off with vba or a udf if this would be an appropriate alternative for you.
    Give a man a fish, he'll eat for a day.
    Teach a man to fish, he'll eat for a lifetime.
    Give a man religion, he'll die praying for a fish.

  4. #4
    Board Regular Lewiy's Avatar
    Join Date
    Jan 2007
    Location
    Hyrule
    Posts
    4,281

    Default

    Diffy, I think the idea is that the OP wants something to look down the left column and find any instances of "hello" and when it does, adds the corresponding value in the right column to a text string.
    Give a man a fish, he'll eat for a day.
    Teach a man to fish, he'll eat for a lifetime.
    Give a man religion, he'll die praying for a fish.

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi Pizzio:

    I agre with Diffy you should describe more clearly what exactly are you trying to accomplish. However, going with Lewiy's understanding, let us have a look at the following ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y070417h1.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    1
    hellono
    2
    hellono hellononoyes
    3
    helloyes goodbye


    no
    4
    goodbyeno
    5
    Sheet12

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    array formula for cells E2:I2 is ... =TRANSPOSE(IF($A$1:$A$4=D2,$B$1:$B$4,""))

    Is this what you are looking for?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Or

    if you are looking for the rsulting entries to be concatenated , then

    ******** ******************** ************************************************************************>
    Microsoft Excel - y070417h1.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    1
    hellono
    2
    hellono hellono,no,yes
    3
    helloyes goodbyeno
    4
    goodbyeno
    5
    Sheet12 (2)

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    array formula in cell E2 is ...

    =SUBSTITUTE(TRIM(MCONCAT(TRANSPOSE(IF($A$1:$A$4=D2,$B$1:$B$4," "))," "))," ",",")

    I hope this helps.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular
    Join Date
    Apr 2005
    Location
    Redmond, WA
    Posts
    214

    Default

    Thanks for the formula Yogi but I seem to be having a special needs moment. I attempted to follow your formula and even copied the entire thing in my spreadsheet and I keep getting the #NAME? value in the cell. I confirmed that there were NO #N/A values or odd data in all referencing cells but i still get the #NAME?, any ideas on what I'm doing wrong?





    ******** ******************** ************************************************************************>
    Microsoft Excel - Unaffiliated Revenue Daniels.xls___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    U
    V
    W
    X
    541
    *********************-******
    542
    *********************-***#NAME?**
    543
    *********************-******
    Combined*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  8. #8
    Board Regular
    Join Date
    Apr 2005
    Location
    Redmond, WA
    Posts
    214

    Default

    okay so i misspelled substitute but even after correcting the error I get the #NAME? value. Can anyone see what I'm doing wrong here?

  9. #9
    Board Regular Lewiy's Avatar
    Join Date
    Jan 2007
    Location
    Hyrule
    Posts
    4,281

    Default

    Is MCONCAT in one of the add-ins? Not sure, but I get #NAME? error when I try to use it on it's own so I suspect so. I've only got Analysis ToolPak installed at the moment so it might be in one of the others.
    Give a man a fish, he'll eat for a day.
    Teach a man to fish, he'll eat for a lifetime.
    Give a man religion, he'll die praying for a fish.

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Sorry, I should have stated that MCONCAT is a function from MoreFunc Add-in and is available for free. There is a link on MrExcel Board to the website for downloading the Add-in.

    If you can not use the MoreFunc Add-in, look at the Board for ACONCAT for VBA code instead.

    I hope this helps.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Page 1 of 2 12 LastLast

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
  •  


DMCA.com