Substitute or Replace Formula Help

[jjm3066]

I have 6 columns and 50 rows of data - lets call this the data set. To the side, I have a list of the 10 possible cell values in vertically in 10 rows - lets call this column 7. In the next column, I have a list of new values. - lets call this column 8 I would like to be able to create a formula, that will say if any cell in the data set equals any value in column 7, then replace with the value that matches the horizontal value in column 8.

 

[VoG]

Maybe

=SUBSTITUTE(A1,G1,H1)

 

[jjm3066]

But how do I create that formula in the cell that I actually want to do the substituting? It comes up as a circular error.

 

[VoG]

Several ways

Tools > Options > Calculation tab > set Iterations to 1

Do the calculation in a spare column then copy those values, select column A, Edit > Paste Special > tick Values, click OK

Use a macro (VBA)

 

[jjm3066]

Didnt quite work out.. returns a zero number.

Any other options?