Look for and return text string, based on certain criteria

New Contributor

Hi, i'm looking for a formula/script that will be able to solve the following.


If cells A12 through A16 contain any one of the text in cells A3:A6, it is to look for the associated text string (wildcard numbers) B3:B6 in the text B12:B17.


Not all cells will return a result.







2 Replies
best response confirmed by Hans Vogelaar (MVP)


=IF(NOT(ISNA(MATCH(A12,$A$3:$A$7,0))),RIGHT(B12,LEN(B12)-SEARCH(INDEX($B$3:$B$7,MATCH(A12,$A$3:$A$7,0)),B12)+1),"leave blank")


Maybe with this formula as shown in the attached file.

That looks great, thanks very much