IF number in A1 is contained in a cell in column B with other text

[surkdidat]

Hi

In Column A I have a series of report numbers.

In Column B I have the file names. Some contain the word Report followed by a number that would appear in Column A

Column A	Column B
123456	iguiguy	Related file 123567
123457	ihohioh
123567	Report 123457
124594	hdiuhds hiohsodhohohoik	Related file 125500
125500	Report 124594

So in Column C I need a formula that looks at the value in column A, and if that number appears in Column B (Always will follow the word "Report") I need to have "Related file xxxxxx" appear. The file number will always be a 6 digit number

Thanks in advance

 

[somesoldiers]

one way with helper col to separate the number from the string in col B
C2: =IFERROR(VALUE(RIGHT(B2,6)),RIGHT(B2,6))
D2: =IFERROR("Related file "&INDEX(A:A,MATCH(A2,C:C,0)),"")

There is a mismatch on you sample above, "Related file 123567" should be in C3 not C2

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2	123456	iguiguy
3	123457	ihohioh	Related file 123567
4	123567	Report 123457
5	124594	hdiuhds hiohsodhohohoik	Related file 125500
6	125500	Report 124594
Checks

Cell Formulas
Range	Formula
C2:C6	C2	=IFNA("Related file "&INDEX($A$2:$A$100,MATCH("*"&A2,$B$2:$B$100,0)),"")

 

[surkdidat]

Apologies for the late feedback, thank you!

How about

Fluff.xlsm
	A	B	C
1
2	123456	iguiguy
3	123457	ihohioh	Related file 123567
4	123567	Report 123457
5	124594	hdiuhds hiohsodhohohoik	Related file 125500
6	125500	Report 124594
Checks

Cell Formulas
Range	Formula
C2:C6	C2	=IFNA("Related file "&INDEX($A$2:$A$100,MATCH("*"&A2,$B$2:$B$100,0)),"")

 

[Fluff]

Glad we could help & thanks for the feedback.