Use of TextSplit Function to a cell with multiple text line separated by delimiter

[nsa1]

Hello,

I have a situation where I want to get number assigned to each name to be printed next to a cell input with multiple names separated by carriage return. ie in my attached example, Group column (Col A) with the formula should find Alex, Brandon, Joe ect from Col E...pick up their number from Col D and print in A2 = 1, 2, 3 and in A3 = 5,6

Thanks in advance!

NA

 

[Peter_SSs]

Is this what you mean?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 08 29.xlsm
	A	B	C	D	E
1	1 2 3	Alex Brandon Joe
2	4 5	Alan Curtis		1	Alex
3				2	Brandon
4				3	Joe
5				4	Alan
6				5	Curtis
nsa1

Cell Formulas
Range	Formula
A1:A2	A1	=TEXTJOIN(CHAR(10),,XLOOKUP(TEXTSPLIT(B1,CHAR(10)),E$2:E$6,D$2:D$6))

 

[nsa1]

=TEXTJOIN(CHAR(10),,XLOOKUP(TEXTSPLIT(B1,CHAR(10)),E$2:E$6,D$2:D$6))

Thanks Peter!

I am not getting 123 instead of 1 Cr 2 Cr 3 like yours...why? I don't know how to insert this as a file here. They only have upload image?

 

[Anonymous1378]

I'm pretty sure that's just a matter of having wrap text enabled?

 

[nsa1]

I'm pretty sure that's just a matter of having wrap text enabled?

Thank you!

 

[Anonymous1378]

Glad to have helped, but probably don't mark my reply as the solution, since it doesn't solve your actual problem but just clarifies the solution by Peter.

 

[Peter_SSs]

I don't know how to insert this as a file here. They only have upload image?

See the second line of my previous post.