filter and textjoin within each row of result

[excel wannabe PP]

Hi,
i have a list of names and places. i am looking to filter the range based off place and return each name on a seperate row. however i want the first and last name joined together.
something like =textjoin(" ',,filter(a2:b5,c2:c=ny)) but keep rows seperate.

thanks!

first	last	state		return like this
bob	arm	ny		bob arm
steve	cohn	nj		marry jay
marry	jay	ny
mark	apple	ca

 

[gargamalebarbosa]

Hi ,
Can you try this ?

 

[Fluff]

Similar idea

Fluff.xlsm
	A	B	C	D	E
1	first	last	state		return like this
2	bob	arm	ny		bob arm
3	steve	cohn	nj		marry jay
4	marry	jay	ny
5	mark	apple	ca
6
Main

Cell Formulas
Range	Formula
E2:E3	E2	=BYROW(FILTER(A2:B100,C2:C100="ny"),LAMBDA(br,TEXTJOIN(" ",,br)))
Dynamic array formulas.

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option for a sample like that ..

22 11 17.xlsm
	A	B	C	D	E
1	first	last	state		return like this
2	bob	arm	ny		bob arm
3	steve	cohn	nj		marry jay
4	marry	jay	ny
5	mark	apple	ca
6
Sheet2 (2)

Cell Formulas
Range	Formula
E2:E3	E2	=FILTER(A2:A10&" "&B2:B10,C2:C10="ny")
Dynamic array formulas.

 

[excel wannabe PP]

thanks everyone! they all work great

 

[Fluff]

Glad we could help & thanks for the feedback.