Multi Line Cell - Find Text String

[CF64]

Hello,

Would there be a way to look into an adjacent cell to determine which line to look on to return the desired text string?

In your formula above you have "//m[3]" with 3 being the line to look at to return the text string on line 3.

I have a table with 4 columns
Column A, Row 2 - a list of text separated with line breaks
Column B, Row 2 - a list of text separated with line breaks
Column C, Row 2 - is this formula which tells me which line in A2, the specific text string listed in C1, appears - =IFERROR(1+LEN(LEFT(a2,SEARCH(c1,a2)-1))-LEN(SUBSTITUTE(LEFT(a2,SEARCH(c1,a2)-1),CHAR(10),"")),0)
Column D, Row 2 - would be a formula to tell me the string of text in B2 corresponding to the line identified in C2 - Could I modify this formula to achieve that?

=FILTERXML("<k><m>"&SUBSTITUTE(A2,CHAR(10),"</m><m>")&"</m></k>","//m[3]")[/CODE]

I tried to insert C2 into "//m[c2]" but received a #VALUE error.

Thank you

	A	B	C	D
1	Questions	Answers	My favorite car	Answer of Interest
2	My favorite snack My favorite animal My favorite car My favorite color	Doritos Dog Corvette Blue	3	Corvette

 

[Fluff]

As there are newer functions in xl, you can do that with using C2 like

Fluff.xlsm
	A	B	C	D
1	Questions	Answers	My favorite car	Answer of Interest
2	My favorite snack My favorite animal My favorite car My favorite color	Doritos Dog Corvette Blue		Corvette
Sheet5

Cell Formulas
Range	Formula
D2	D2	=XLOOKUP(C1,TEXTSPLIT(A2,CHAR(10)),TEXTSPLIT(B2,CHAR(10)))

 

[CF64]

As there are newer functions in xl, you can do that with using C2 like

Fluff.xlsm
	A	B	C	D
1	Questions	Answers	My favorite car	Answer of Interest
2	My favorite snack My favorite animal My favorite car My favorite color	Doritos Dog Corvette Blue		Corvette
Sheet5

Cell Formulas
Range	Formula
D2	D2	=XLOOKUP(C1,TEXTSPLIT(A2,CHAR(10)),TEXTSPLIT(B2,CHAR(10)))

Awesome! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.