1

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

  • share a copy of your sheet with example of desired output – user0 Aug 30 at 16:06
1

Try REGEXPLACE instead :

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

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

0


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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.