Forum Discussion
Find and replace
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. Examp...
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)))