VLOOKUP formula for a multiline cells range

[balasat]

Need your support here..am trying to search a specific text in a multiline valued cell and return corresponding value in the same row
In the first source table, i have serial number with multiple line values in cell and corresponding title for serial no's
in other table, have one serial no value in each cell, now i need a formula to search serial no in column E to find matching cell in column B and return value from Column C

thank you!

 

[balasat]

Serial​	Title​
123456 56789 3547924852 342984732195​	Serial no 3​
1234567 5678900 3547924852111 342984732195222​	Serial no 2​
ty45269 5sadfdsfe​	Serial no 1​

Serial No	Output forumla needed	Desired Output
123456	Search value 123456 in column 'B' and return result from column 'C'​	Serial no 3​
56789	Search value 56789 in column 'B' and return result from column 'C'​	Serial no 3​
3547924852	Search value 3547924852 in column 'B' and return result from column 'C'​	Serial no 3​
342984732195	Search value 342984732195 in column 'B' and return result from column 'C'​	Serial no 3​
1234567	Search value 1234567 in column 'B' and return result from column 'C'​	Serial no 2​
5678900	Search value 5678900 in column 'B' and return result from column 'C'​	Serial no 2​
3547924852111	Search value 3547924852111 in column 'B' and return result from column 'C'​	Serial no 2​
342984732195222	Search value 342984732195222 in column 'B' and return result from column 'C'​	Serial no 2​
ty45269	Search value ty45269 in column 'B' and return result from column 'C'​	Serial no 1​
5sadfdsfe	Search value 5sadfdsfe in column 'B' and return result from column 'C'​	Serial no 1​

 

[JvdV]

Book1
	A	B
1	Serial	Title
2	123456 56789 3547924852 342984732195	Serial no 3
3	1234567 5678900 3547924852111 342984732195222	Serial no 2
4	ty45269 5sadfdsfe	Serial no 1
5
6	Serial No
7	123456	Serial no 3
8	56789	Serial no 3
9	3547924852	Serial no 3
10	342984732195	Serial no 3
11	1234567	Serial no 2
12	5678900	Serial no 2
13	3547924852111	Serial no 2
14	342984732195222	Serial no 2
15	ty45269	Serial no 1
16	5sadfdsfe	Serial no 1
Sheet2

Cell Formulas
Range	Formula
B7:B16	B7	=XLOOKUP("*"&CHAR(10)&A7:A16&CHAR(10)&"*",CHAR(10)&A2:A4&CHAR(10),B2:B4,,2)
Dynamic array formulas.

 

[balasat]

Thanks a ton for the help