## If you think your needs might change, use a lookup table

The website *exceljet.com* had a very good way of handling this by Dave Bruns. This works very well when you'd like to see your corrections laid out in a table, and edit them or change them.

It avoids *hard coding* all the corrections into formulas, which can be hard to verify (and impossible when printed on paper).

It works by using the `INDEX`

function. Create a **lookup table**:

```
before | after
--------- | ---------
APPLE | ALICE
BANANA | BOB
CHERRY | CARL
KIWI | KENNY
```

- Use
`Insert:Table`

to tell Excel it's a table, and let's name it `corrections`

:

Then in your main table, use this formula:

```
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
J2,
INDEX(corrections[before],1),INDEX(corrections[after],1)),
INDEX(corrections[before],2),INDEX(corrections[after],2)),
INDEX(corrections[before],3),INDEX(corrections[after],3)),
INDEX(corrections[before],4),INDEX(corrections[after],4))
```

Yes, you're allowed to have 'returns' (linefeeds) in a formula. Excel ignores them, but they make it much easier to read.

There's no magic in the numbers 1,2,3,4... they are the row numbers in the `corrections`

table for the `INDEX`

function.

The number of rows (4) in the lookup table is the same as the number 4 in the formula. If you need more rows, read on:

## Expand as necessary

If you need to add more rows to your `corrections`

table, it's very easy. Just modify your replacement formula. Let's say you want to go up to eight rows:

```
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
J2,
INDEX(corrections[before],1),INDEX(corrections[after],1)),
INDEX(corrections[before],2),INDEX(corrections[after],2)),
INDEX(corrections[before],3),INDEX(corrections[after],3)),
INDEX(corrections[before],4),INDEX(corrections[after],4)),
INDEX(corrections[before],5),INDEX(corrections[after],5)),
INDEX(corrections[before],6),INDEX(corrections[after],6)),
INDEX(corrections[before],7),INDEX(corrections[after],7)),
INDEX(corrections[before],8),INDEX(corrections[after],8))
```