Replace Maiden Name with Married Name

[kedzugan]

Say I have a worksheet with sales listed by name. Some of the names have changed due to marriage, and I have a reference list of maiden and married names. How can I automatically change the maiden name to the married name based on my reference list?

[dosydos]

CTRL+H

or

REPLACE() or substitiute() function

Provide an example workbook if you need more in depth answer

[kedzugan]

I understand how to use the find and replace function, but I don't want to have to do this for each individual name, as there are many of them. I want excel to look at an entire list of names and compare them to a list of married names. Then excel would update any name that has changed according to this reference list. I have uploaded a simplified example to my original post which hopefully makes my question clearer.

[sandy666]

with Power Query

[kedzugan]

Thanks. This looks like what I want. I'm just going to have to spend some time figuring out how to actually do it. I've watched a few tutorials on Power Query, but I haven't been able to wrap my head around it yet.

[Glenn Kennedy]

Option 1:
=IFERROR(VLOOKUP(A2,I:J,2,FALSE),A2)

Option 2:
=IFERROR(VLOOKUP(A24,I:J,2,FALSE),"")

see file.

[sandy666]

Thanks. This looks like what I want. I'm just going to have to spend some time figuring out how to actually do it. I've watched a few tutorials on Power Query, but I haven't been able to wrap my head around it yet.

here is almost everything about Power Query - Power Query documentation

[kedzugan]

Thank you! I can apply this right away. I'm still planning on becoming familiar with power query, because I see the long range benefits.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.