# Multiple substitutions in a table

Suppose we have a table like the following :

A B C D E F G
1 17-01 DO 22-06 DO 17-01 22-06 22-06
2 22-06 22-06 17-01 DO 22-06 09-17 09-17
3 09-17 DO 22-06 22-06 DO 17-01 17-01
4 17-01 22-06 17-01 DO 22-06 DO 22-06
5 22-06 DO 09-17 09-17 09-17 22-06 09-17
6 09-17 09-17 DO 22-06 DO 09-17 22-06

And I would like to substitute the following values more efficiently.

• "09-17" ---> "07-15".
• "17-01" ---> "15-23"
• "22-06" ---> "20-04"

I managed to do it with the following solution (not so efficiently as it makes lot of loops)

``````=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({B2:G6},"09-17","07-15"),"17-01","15-23"),"22-06","20-04"))
``````

I was wondering how this can be done more efficiently when the dimensions of the table are too large.

Any idea how to limit unnecessary repetitions?

• Will this data be continuously updated? If not, I would just to a find and replace. Commented Apr 30 at 17:54

Put your substitutions in your sheet, for example, in Columns I and J:

A      B      C      D      E      F      G      H I       J
1 17-01 DO 22-06 DO 17-01 22-06 22-06 Find Replace
2 22-06 22-06 17-01 DO 22-06 09-17 09-17 09-17 07-15
3 09-17 DO 22-06 22-06 DO 17-01 17-01 17-01 15-23
4 17-01 22-06 17-01 DO 22-06 DO 22-06 22-06 20-04
5 22-06 DO 09-17 09-17 09-17 22-06 09-17
6 09-17 09-17 DO 22-06 DO 09-17 22-06

Use the following formula. You can constrain the ranges if you prefer. eg. `I2:J4` instead of `I2:J`, or `A1:G700` instead of `A1:G`

``````=ARRAYFORMULA(IFNA(VLOOKUP(A1:G, I2:J, 2, 0), A1:G))
``````

You could alternatively hard-code the values if you prefer:

``````=ARRAYFORMULA(IFNA(VLOOKUP(A1:G,
{"09-17","07-15"; "17-01","15-23"; "22-06","20-04"},
2, 0), A1:G))
``````

# Faster Approach

The following approach is about 50% faster than the VLOOKUP approach and about 2x faster than the nested SUBSTITUTE approach. Depending on the size of the source range, the speed difference may not be noticeable:

``````ARRAYFORMULA(
IF(A1:G="09-17","07-15",
IF(A1:G="17-01","15-23",
IF(A1:G="22-06","20-04", A1:G))))
``````