Searching a list and replacing value if any item matches any item in the other list in Google Sheets

Question

I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't seem to find a built-in function that does this as they all assume a single value and not a list of values to match against. For example, I tried:

=SUBSTITUTE([source value],'list of values to look for the source value in'!A:A,"static string")

but this didn't trigger, no replacement happened. (source_value should have been changed to 'static string', or I should have the static_string in the next cell over in a new column).

Answer 1

Try REGEXPLACE instead :

=REGEXREPLACE([source value],TEXTJOIN("|",TRUE,'list of values to look for the source value in'!A:A),"static string")

Answer 2

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, C2:D, 2, 0), A2:A))

---

=ARRAYFORMULA(REGEXREPLACE(""&A2:A, "(^"&TEXTJOIN("$|^", 1, C2:C)&"$)", "$1_♥"))