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).