Google Sheets formula to find partial matching value in a separate sheet and return the matched value in another column

Question

I need the formula in Google Sheets to find any values in a cell that match values in cells from another Sheet, and copy the matched value in the adjacent cell. Preferably I need an array formula to copy down all cells.

Here is my sample sheet:

https://docs.google.com/spreadsheets/d/1jCzLUPQiykkgVF1JhNGGFdnoGnukVdZsdduDTfyCWfc/edit?usp=sharing

Answer 1

See if this works

=ArrayFormula(if(len(A2:A), iferror(PROPER(regexextract(A2:A, "(?i)"&textjoin("|", 1, Sheet2!A2:A))), "No Match"),))

(Formula entered in cell C2 in the spreadsheet you shared).