Issue

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.

Solution

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.

Note

Thanks to rizvisa1 for this tip on the forum.