I will explain it here again:

In Column A I have:
Superfast Car [Lexus]
Boat in the Sea [Whaler,Blue]
Wooden Table [Brown]
Superfast Car [Lexus]
Superfast Car [Lexus]

In Column B:
Superfast Car
Boat in Sea
Wooden Table

In Column C:
Car
Boat
Table

Now, in column D I want:
If Column A contains the word "Superfast Car", then use the word "Car" instead and keep the word [Lexus]. It found 3 matches for the word Superfast Car. Others will be changed too.

Therefore, column D would be as follows:
Car [Lexus]
Boat [Whaler,Blue]
Table [Brown]
Car [Lexus]
Car [Lexus]

Basically it is replacing the keywords in Column A to a short word in Column D.

I did it once long time ago and it was a short formula, but I can't seem to make it work this time.