Moving data in bulk

[gsmillie]

Hi All

Not new to Excel but I've only ever used it for very basic things so need some advice please.

I have 3 sets of data. Each set has a company name.

Set 1 of data is the master set with all company names but no emails.

Set 2 and set 3 have only a few companies but they have some email addresses with them.

I need to transfer the email addresses from set 2 and 3 into set 1.

Doing this manually will take a very long time so I am wondering if there's a way to do it in bulk? If this forum allows uploads I can upload a data set.

Thanks in advance.

 

[JamesCanale]

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H
1	Set1	looked up email		Set2	email		Set3	email
2	Pepsi	a@pepsi.com		Pepsi	a@pepsi.com		Coke	z@coke.com
3	Coke	z@coke.com		Ford	b@ford.com		Chevy	y@chevy.com
4	Ford	b@ford.com		Honda	c@honda.com		Intel	x@intel.com
5	Chevy	y@chevy.com
6	Honda	c@honda.com
7	Mac
8	Intel	x@intel.com
Sheet15

Cell Formulas
Range	Formula
B2:B8	B2	=IFNA(VLOOKUP(A2:A8,VSTACK(D2:E4,G2:H4),2,FALSE),"")
Dynamic array formulas.

 

[gsmillie]

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H
1	Set1	looked up email		Set2	email		Set3	email
2	Pepsi	a@pepsi.com		Pepsi	a@pepsi.com		Coke	z@coke.com
3	Coke	z@coke.com		Ford	b@ford.com		Chevy	y@chevy.com
4	Ford	b@ford.com		Honda	c@honda.com		Intel	x@intel.com
5	Chevy	y@chevy.com
6	Honda	c@honda.com
7	Mac
8	Intel	x@intel.com
Sheet15

Cell Formulas
Range	Formula
B2:B8	B2	=IFNA(VLOOKUP(A2:A8,VSTACK(D2:E4,G2:H4),2,FALSE),"")
Dynamic array formulas.

James - super helpful. Thank you very, very much.

Please may you advise one more thing?

If I am to delete the original email address from set 2 or set 3, it deletes from set 1. Is there a workaround for this?

Thanks again.

 

[JamesCanale]

You'd have to paste the values over the new formula in column B.