TEXTJOIN formula to search for value with multiple results

[Laura1608]

Hello all,

I'm trying to apply the TEXTJOIN formula to search for a value and get multiple results.

My data looks like this:

	A	B	C	D	E	F
1	Column1	Column2	Column3		Unique	Values
2	1	A	A1		A	..
3	2	A	A2		B	...
4	3	A	A3
5	4	B	B1
6	5	B	B2

What I want, is to look for the unique values in column2 (e.g. A) and then print all the matching values from column3 (e.g. A1, A2 and A3), in one cell or in different columns.

I tried the following formula (Excel 2019) but it gives me an error on the = sign:

=TEXTJOIN(",";TRUE;IF(E2=B:B,C:C,""))

Does someone know what I'm missing here?

Thanks!

Laura

[#2 · Accepted solution]

@Laura1608 

You separate the arguments inconsistently - a mixture of commas and semicolons.

Do you use point as decimal separator? If so:

=TEXTJOIN(",",TRUE,IF(E2=B:B,C:C,""))

But if you use comma as decimal separator:

=TEXTJOIN(",";TRUE;IF(E2=B:B;C:C;""))

[#3]

@Laura1608 

If you have Microsoft 365 or Office 2021, you can also use

=TEXTJOIN(",",TRUE,FILTER(C:C,B:B=E2))

or

=TEXTJOIN(",";TRUE;FILTER(C:C;B:B=E2))

depending on your decimal separator.

[#4]

@Hans VogelaarThanks, that helped!

And for other readers: don't forget to presss CTRL+SHIFT+ENTER afterwards, or it won't work.