Substitute multiple data with a table, instead of a long =substitute string.

I have a column of words that I would like to automatically substitute to other words.

BAD Words	GOOD Words
AAA	111
BBB	222
CCC	333

...etc


I currently use a Substitute formula, but it's grown veeeeeeery long. (ie, =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE... x38).

I found out you can create a table, with the bad words in columnA and good words in column B, then use the following formula to automatically convert them:

Formula:

Please Login or Register  to view this content.

That works fine... except if it finds a word not in the table, it returns an #N/A error.

Using the =Substitute string works better because if it finds a Bad Word NOT in the substitute string, it just carries over the original. The Table Formula doesn't do this.

Can it? Is there something you can add that basically says "Convert Bad Word to Good Word using Table1 as reference, but if it's not in Table1, just use the Bad Word"?

Try:
=iferror(INDEX(Table1["Good Words"],MATCH(A1,Table1["Bad Words"],0)),A1)