Concatenate multiple values from table row into one cell

[BraveDaveIA]

I hope someone can help me with this, seemingly simple(?), need:

I need a formula to search through a table's row for multiple instances of a certain lookup value, then retrieve an associated retrieval value from a different row in that table, and concatenate all those retrieval values into a single cell, delimited by commas.

	A	B	C	D	E	F
1		Oranges	Apples	Pears	Bananas	Grapes
2	Oranges, Pears	y		y
3	Apples, Bananas, Grapes		y		y	y
4	Apples, Pears		y	y
5	Oranges, Apples, Bananas	y	y		y

<tbody>
</tbody>

In the sample table above, the formula would reside in cells A2 through A5. It would be pulling the retrieval values from row 1, based on a "y" appearing in any of the cells B2:F5.

It is not necessary the concatenated retrieval values appear alphabetized.

In reality, my tables will be up to 20 columns and up to 2,000 rows.

Here's hoping there's a way.

 

[BraveDaveIA]

If you always want a ", " at the end of the string, then delete the "If Right(JoinFruits, 2) = ", " Then JoinFruits = Left(JoinFruits, Len(JoinFruits) - 2)".

If a row has no Y/y, then the function returns "".

Is that you want?

It is indeed.
Thanks!

(You've inspired me to try to learn a bit of VBA.)