Excel spreadsheet to overwrite cells using a table of values

Question

I would like to write an excel function, that will replace some cells, containing text, with values from the table. The text in the cell gives correspondence to the table.

For example:

Here i have my cells that I would like to replace/overwrite. I would like to use the Table of values for matching to match my text1_1 with the newtext in Table of values for replacement at (1,1), and so on.

How can I accomplish this?

What I though is that I do the following:

1. I know that text1_1 is in column 1 in cell to replace table.
2. So, I know that text1_1 will be in the 1st row of Table of values for matching, I only need to find the column there.
3. Once, I have the row number r and column number c in Table of values for matching, then I can use the value at Table of values for replacement at (r - displacement, c) to replace my text1_1 in Cells to replace.

It is possible, that there is a more efficient solution, than what I thought out, so in that case, I am very open to see it.

Which functions should I use? If there is no straightforward way to do it, then could somebody give me some guidance?

Answer 1

Here is a suggestion, assuming I understand your question correctly.

As mentioned in the comments, there is no way to place a formula within a cell that will use the contents of the same cell, because it already contains the formula. What you can do is create a new table to fetch out all the new values based on the Table of values for matching. However, I assume here (using your example) that you know in which column / row to look in the tables. In your example the column number of cell to replace was the row number to look at in Table of values for matching, so my solution fits this situation. Nevertheless, you can easily edit it to fit another need, as long as you now in which column / row to look. So lets start:

1. Create a new table that match the cell to replace table in size.
2. In the most upper left cell paste the following formula: =INDEX($G$3:$M$6,A$10,MATCH(A3,OFFSET($G$10:$M$10,A$10-1,0),0))
3. Drag this formula to all the other cells, and you will see the new values that match the values in Table of values for replacement

Tell me if it answers your question ;)