Search Range For Text That Matches Part of String After Last Comma

[djmyers]

I am trying to match partial text in a string with text from a range on another sheet.

The text fragment in the string is usually found after the last comma. For example:
P38 Some Noise Text > Other Noise Text > 12-3456, 7890 Text I Want

The range on the other sheet (Sheet2!$B$2:$B$505) will sometimes contain ONLY this alphanumeric (7890 Text I Want) somewhere in the range, but more often it will be found in a non-matching string, usually at the beginning of the string. Example:
7890 Text I Want MA1309-054-055

I'm struggling to find a formula that will accomplish this. If the partial match is found, I'd like the formula to return the string fragment that matches (7890 Text I Want), or "no" if not found.

 

[DanteAmor]

Try this:

varios 12jul2020 filtervalues.xlsm
	A	B
1
2	P38 Some Noise Text > Other Noise Text > 12-3456, 7890 Text I Want	7890 Text I Want MA1309-054-055
3	P38 Some Noise Text > Other Noise Text, > 12-3456, some text	No
Hoja6

Cell Formulas
Range	Formula
B2:B3	B2	=IFERROR(VLOOKUP("*"&TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",255)),255))&"*",Sheet2!$B$2:$B$600,1,0),"No")

 

[djmyers]

Your formula works like a champ, DanteAmor! Thank you very much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.