vlookup with multiple text value return

[Pizzio]

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

 

[Diffy]

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

 

[Lewiy]

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.

 

[Lewiy]

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.

 

[Yogi Anand]

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

y070417h1.xls
	A	B	C	D	E	F	G	H
1	hello	no
2	hello	no		hello	no	no	yes
3	hello	yes		goodbye				no
4	goodbye	no
5
Sheet12

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?

 

[Yogi Anand]

Or

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

y070417h1.xls
	A	B	C	D	E	F
1	hello	no
2	hello	no		hello	no,no,yes
3	hello	yes		goodbye	no
4	goodbye	no
5
Sheet12 (2)

array formula in cell E2 is ...

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

I hope this helps.

 

[Pizzio]

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?

Unaffiliated Revenue Daniels.xls
	U	V	W	X
541	-
542	-	#NAME?
543	-
Combined

 

[Pizzio]

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?

 

[Lewiy]

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.

 

[Yogi Anand]

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.