Find and replace

Copper Contributor

Hi there.

 

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.

 

Example:

Column A                Column B                       Column C             

440                              534                               53

570                              44                                644

534                              57                                578

 

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:

 

Column A                Column B                       Column C                   Column D         

440                              534                               53                                 6440

570                              44                                644                                5780

534                              57                                578                                53

 

If someone could help with this, that would be greatly appreciated.

 

Rachel

2 Replies

@Rachwar30 

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:

 

=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)))

 

If you want the result to be a number:

 

=--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)))

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.