How to lookup and replace string

[singhabhijitkumar]

I have a lookup table with two columns, viz., OLD VALUE and NEW VALUE. In another sheet I have strings populated in one column. If the string contains OLD VALUE from lookup table, then that OLD VALUE should be replace with the NEW VALUE in the string.

I have demostrated the same using the attached excel.

[ajm123456789]

=IF('lookup table'!B2=0, "This is my " &'lookup table'!A2, "This is my " &'lookup table'!B2)

[singhabhijitkumar]

no no no you are hardcoding "This is my " in your formula. The formula should search and replace lookup values from string.

[vlady]

try this one index/match

replaceoldbynew.xlsx

[singhabhijitkumar]

It is close. But it works only in case of exact match. Fails when the text to be replaced is a part of the string.

[vlady]

you can try to have wild cards for the match()

=INDEX('lookup table'!$B$2:$B$3,MATCH("*"&'before update'!A2&"*",'lookup table'!$A$2:$A$3,0))

if this doesn't work put a sample data on how did those values are organized to be more specific.

[singhabhijitkumar]

Hi I have attached your excel with sample data and the new formula using wildcard. It is still failing to replace.

[vlady]

maybe this one???

replace.xlsx

[singhabhijitkumar]

Bingo! Fantastic! Yes this is what I am looking for.