I came across a scenario recently where I need to do some vlookup task but I want to do it through vba. Below is the scenario;

Workbook1, only column A is populated, column B is empty.

A B
abc
bcd
efg
hig
stu


Workbook2 have both the columns populated.

A B
abc x
abc y
bcd y
bcd z
bcd e
efg z
hig e
jkl e
mno e
pqr x

Now I want to search the value of cell 1 of workbook1 in workbook2 and if found paste the corresponding value in cell2 workbook1. But if the values are more then one then insert a row and paste the corresponding value and if not found leave it as it is. Something like this, see below;


A B
abc x
abc y
bcd y
bcd z
bcd e
efg z
hig e
stu


Any help would be really appreciated.