Using REGEXREPLACE() and VLOOKUP() to search and replace using a pairwise list

Question

Suppose I have a list of people with unique first and last names, in two columns, B and C. See example spreadsheet here.

Cell E6 contains the following text:

Firstname1 would like to say hello to Firstname2. Firstname3 also says hello.

I would like to write an in-cell formula (i.e., not using custom functions) that goes through the text and replaces first names with the corresponding last names. The result should look like this:

Lastname1 would like to say hello to Lastname2. Lastname3 also says hello.

I tried the following, but it seems not to work:

=REGEXREPLACE(E6,"(Firstname[0-9])",VLOOKUP("$1",B:C,2))

Any ideas? It's important that the solution not use a custom formula.

Answer 1

Yes you need additoinally add a regexextract step:

=regexreplace(A1,"(Firstname[0-9])",VLOOKUP(regexextract( A1,"Firstname[0-9]"),B:C,2,false))

Answer 2

=ARRAYFORMULA(JOIN(" ",IFERROR(VLOOKUP(SPLIT(E6," "),B:C,2,0),SPLIT(E6," "))))

• SPLIT E6 by space to give a array
• VLOOKUP the corresponding words in B:C
• IF #N/A, Use the SPLITted array
• JOIN back the modified array