Find and replace

Question

Hi there.&nbsp;In excel I would like to find values from one column anywhere within another column and replace with corresponding values from a third column and return it in a fourth column.&nbsp;Example:Column A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Column C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;440&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 534&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;53570&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 44&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 644534&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 57&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 578&nbsp;I would like to find value from B1 (534) anywhere in column A (A3) and replace it with the corresponding value in C1 (53) in D1. I would like to repeat this for B2, B3 etc. and there are over 1000 rows. So the results should look like this:&nbsp;Column A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Column C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Column D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;440&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 534&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;53&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6440570&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 44&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 644&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5780534&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 57&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 578&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 53&nbsp;If someone could help with this, that would be greatly appreciated.&nbsp;Rachel

hansvogelaar · Answer

Rachwar30&nbsp;
Assuming that the data begin in row 2, enter the following formula in D2, adjust the ranges as needed, then fill down.
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter:
&nbsp;
=SUBSTITUTE(A2, INDEX($B$2:$B$4, MATCH(TRUE, ISNUMBER(SEARCH($B$2:$B$4, A2)), 0)), INDEX($C$2:$C$4, MATCH(TRUE, ISNUMBER(SEARCH($B$2:$B$4, A2)), 0)))
&nbsp;
If you want the result to be a number:
&nbsp;
=--SUBSTITUTE(A2, INDEX($B$2:$B$4, MATCH(TRUE, ISNUMBER(SEARCH($B$2:$B$4, A2)), 0)), INDEX($C$2:$C$4, MATCH(TRUE, ISNUMBER(SEARCH($B$2:$B$4, A2)), 0)))

mtarler · Answer

I think I'm reading it differently. I think you want to replace the value in Col D with the value from Col C if Col A has a match in Col B. So for row 3 the number 534 is in Col B so use that row's value in Col C (53) in Col D. That said what do you use if there isn't a match? A formula can't 'replace' a value it can only calculate and display a value. So if the values in Col D are calculated then we can add a conditional to that formula, otherwise you will need to use Col E to display col D OR col C from the other row. Something like this in E2:
=IFERROR(VLOOKUP(A2,B2:C100,2,0),D2)
So basically if the value in col A in this row can be found in Col B then return Col C, but if it isn't found then return the value in D2.

Forum Discussion

Find and replace

2 Replies

Resources