Excel - Replace cell value based on a value in another column

Question

I am wondering if there is a way to replace a cell value from another list without having to write a VB script.

Here is the problem I am trying to resolve:

I have last names in a column:

Smith
Jones
Taylor
etc.

I have another column with IDs e.g.

Smith_ID
Taylor_B
Jones_C

I would like to replace Smith with Smith_ID, Jones with Jones_C etc. Obviously my list is much longer than 3 entries.

Answer 1

He can do it.

• Extract column IDs with "_" character by "Text to Columns"

A column
Smith
Jones
Taylor

B column
Smith
Taylor
Jones

C column
ID
B
C

Now write to following formula

=CONCATENATE(A1;"_";VLOOKUP(A1;$B$1:$C$3;2;FALSE))

I hope you do