Excel - Macro to search and replace


I have two workbooks with identical setup.

item name address 1 address 2 address 3

x 12 34 56 

I need a macro to search for "x" in the other workbook and replace address 1, 2 and 3 with the addresses in that other workbook. I also need the macro to continue to do this for every item listed in the "item name" column.


Let's say you do if in E, F and G. This gives you address from other book. Then you can use copy and paste special to make it value

then you can delete original b, c and d

you would be using something like this

=IF(ISERROR(VLOOKUP(A2,[Book1.xls]Sheet1!$A:$D,2, false)), A2,VLOOKUP(A2,[Book1.xls]Sheet1!$A:$D,2, false)) 

which basically says if the value is not found in book2, then keep original value else get the updated value.


Thanks to rizvisa1 for this tip on the forum.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Macro to search and replace », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).