vlookup with multiple text value return

Thanks:  0
Likes:  0

# Thread: vlookup with multiple text value return

1. ## 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. I am having a hard time understanding your example. Can you clarify, and maybe give a better example with more explanation?

3. 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.

4. 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.

5. 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
 E2F2G2H2E3F3G3H3 =

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?

6. 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
 E2E3F3 =

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.

7. 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
 U541U542V542U543 =

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. 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. 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.

10.

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

I hope this helps.

## User Tag List

#### Posting Permissions

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