Match part of the text and replace with another text in excel

Question

I've data table with list of names which are having few differences. I am trying to change those text to similar name within data table as image in below.

If part of the text in cells in data table are matching with the "Abbreviations" list(Col-AK) then Data table text must replace with the text in "To be replaced" (Col-AL) into "Expected Return Table". I've tried using may different functions to accomplish my ultimate target, but none of them is giving perfect answer. Can anybody help me with this problem.

Answer 1

I found an answer to my problem. I could do this using below formula.

Step 01:- I've used below static formula at first to identify the dynamic formula

=IF(LEFT(AC2,4)=$AK$2,$AL$2,AC2)

Step 02:- Then I've used Index function to return the first text in abbreviation list which is Iodine by only changing partial of the formula to return only single value avoiding others. Ex: Firstly I considered only about the Iodine in the list. This will only replace Iodine into the expected data table.

=IF(LEFT(AC2,4)=INDEX($AK$2:$AK$11,1),$AL$2,AC2)

Step 03:- Then row_num turned into dynamic and expanded the return text data into range($AL$2:$AL$11) from single($AL$2) using Match & Left function as below.

=IF(LEFT(AC2,4)=INDEX($AK$2:$AK$11,MATCH(LEFT(AC2,4),$AK$2:$AK$11,0)),INDEX($AL$2:$AL$11,MATCH(LEFT(AC2,4),$AK$2:$AK$11,0)),AC2)

Step 04:- Finaly ignore errors using IFERROR Function.

=IFERROR(IF(LEFT(AC2,4)=INDEX($AK$2:$AK$11,MATCH(LEFT(AC2,4),$AK$2:$AK$11,0)),INDEX($AL$2:$AL$11,MATCH(LEFT(AC2,4),$AK$2:$AK$11,0)),AC2),"")

Expected Return Data Table as shown below.