Aug 23 2022 10:42 AM
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
Aug 23 2022 01:26 PM
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)))
Aug 23 2022 07:13 PM