Excel Search and Replace string with vlookup

Question

I have a excel sheet where I have columns that say "I like TSX but I prefer 350" "I like beamer but I am fan of S-Class"

I want to replace this with "I like Acura but I prefer BMW" "I like BMW but I am fan of Mercedes"

I have another excel sheet with the match and replace values. For example TSX, Acura TL, Acura 350 BMW Beamer, BMW

etc.

I know how to use vlookup, but this is way advance for me. Any help? Thanks you.

Answer 1

Rather than use vlookup at all, if you don't have many types of cars you could simply use SUBSTITUTE. Works as

=SUBSTITUTE(A1, "TSX", "ACURA")

Then wrap it many times, once for each possibility:

=SUBSTITUTE(SUBSTITUTE(A1, "TSX", "ACURA"),"S-CLASS", "MERCEDES"))

If you have many possibilities, this will either need 2 helper columns, or vba. Let me know if that's the case. Basically without vba you will need to search for the first term in 1 column, then put the 2nd term in the second formula, then replace the 1st term with a vlookup'd equivilent, then replace the 2nd term with a vlookuo'd equivilent (using substitute as above).

Edit

Reading the q again it seems there are many possibilities, so we will need to use two helper columns and areay formulas to solve. VBA is likely better, but In on my phone so cant error test anything, and you might not want vba anyway.

The principle for these helper columns is going to be to find which 2 vehicles exist in the text. Once you know which two exist, you can simply replace both as above.

The first stage of the formula we will be putting in colns B and C, (assuming your 'vlookup text' is in columns A and B on sheet2 from rows 1 to 100) is:

=If(iserror(search($A1,sheet2!$A$1:$A100)),"",row(sheet2!$A$1:$A$100))

This is an array formula, which works on each cell in the range you give it. To confirm it, press CTRL + SHIFT + ENTER instead of just ENTER. It gives you an array of results, that you need to collapse into a single answer. so in column B, wrap it in a MIN function. For column C, wrap it in a Max function.

So now column B shows the row number of 1 matching term, and row C shows the row number of the 2nd matching term.

So now in column D, we know the row number of the first match, the first replacement term, and thend match and replacement term. Replace everything as follows:

=Substitute(substitute(A1,INDEX(SHEET2!$A$1:$A$100, B1),INDEX(SHEET2!$B$1:$B$100, C1)),INDEX(SHEET2!$A$1:$A$100, C1),INDEX(SHEET2!$B$1:$B$100, B1)))

Make sure you confirm with CTRL SHIFT ENTER instead of just enter.

Apologies if this isnt formatted 100%; Im on my phone and will error check it later if it causes problems. Let me know if you have questions about how this works.