VBA Lookup One Value To Copy and Paste Multiple Values

[chriseponymous]

Hi Kind People,

I hope you are well.

I am on the scrounge for some assistance if possible. I need a VBA that works as a Lookup, unfortunately my knowledge is very limited.

I have information in two columns, for argument's sake A5:A30 and B5:B30. The first has a name of a site, the second contains the email address' of people who work there, the name of the site may be repeated numerous times.

When the name of the site is inputed into cell A1 I need the VBA to automatically find and paste the relevant email address' in a range, say C5:C30 Thank you very much for your assistance!

 

[duggie33]

This may work for you. If your version does not handle the dynamic arrays, hit Ctrl-Shift-Enter when done editing the formula and it should work.

Book1
	A	B	C
1	BD COMPANY
2
3
4
5	BD COMPANY	bob@bd.com	bob@bd.com
6	ABC COMPANY	jim@abc.com
7	ABC COMPANY	joe@abc.com
8	BD COMPANY	jack@bd.com	jack@bd.com
9	ABC COMPANY	john@abc.com
10	BD COMPANY	buck@bd.com	buck@bd.com
11	BD COMPANY	ginny@bd.com	ginny@bd.com
12	BD COMPANY	melissa@bd.com	melissa@bd.com
13	BD COMPANY	kelly@bd.com	kelly@bd.com
14	ABC COMPANY	pat@abc.com
15	ABC COMPANY	tammy@abc.com
16	ABC COMPANY	tommy@abc.com
17	BD COMPANY	jeremiah@bd.com	jeremiah@bd.com
18	BD COMPANY	becky@bd.com	becky@bd.com
19	BD COMPANY	bobby@bd.com	bobby@bd.com
20	BD COMPANY	jeff@bd.com	jeff@bd.com
21	ABC COMPANY	bryan@abc.com
22	ABC COMPANY	paul@abc.com
23	ABC COMPANY	jackie@abc.com
24	ABC COMPANY	jerry@abc.com
25	ABC COMPANY	bryce@abc.com
26	ABC COMPANY	taylor@abc.com
27	ABC COMPANY	austin@abc.com
28	ABC COMPANY	edward@abc.com
29	ABC COMPANY	ashlyn@abc.com
30	BD COMPANY	jewel@bd.com	jewel@bd.com
Sheet3

Cell Formulas
Range	Formula
C5:C30	C5	=IF(A5:A30=$A$1,B5:B30,"")
Dynamic array formulas.

Hope that helps,

Doug

 

[chriseponymous]

This may work for you. If your version does not handle the dynamic arrays, hit Ctrl-Shift-Enter when done editing the formula and it should work.

Book1
	A	B	C
1	BD COMPANY
2
3
4
5	BD COMPANY	bob@bd.com	bob@bd.com
6	ABC COMPANY	jim@abc.com
7	ABC COMPANY	joe@abc.com
8	BD COMPANY	jack@bd.com	jack@bd.com
9	ABC COMPANY	john@abc.com
10	BD COMPANY	buck@bd.com	buck@bd.com
11	BD COMPANY	ginny@bd.com	ginny@bd.com
12	BD COMPANY	melissa@bd.com	melissa@bd.com
13	BD COMPANY	kelly@bd.com	kelly@bd.com
14	ABC COMPANY	pat@abc.com
15	ABC COMPANY	tammy@abc.com
16	ABC COMPANY	tommy@abc.com
17	BD COMPANY	jeremiah@bd.com	jeremiah@bd.com
18	BD COMPANY	becky@bd.com	becky@bd.com
19	BD COMPANY	bobby@bd.com	bobby@bd.com
20	BD COMPANY	jeff@bd.com	jeff@bd.com
21	ABC COMPANY	bryan@abc.com
22	ABC COMPANY	paul@abc.com
23	ABC COMPANY	jackie@abc.com
24	ABC COMPANY	jerry@abc.com
25	ABC COMPANY	bryce@abc.com
26	ABC COMPANY	taylor@abc.com
27	ABC COMPANY	austin@abc.com
28	ABC COMPANY	edward@abc.com
29	ABC COMPANY	ashlyn@abc.com
30	BD COMPANY	jewel@bd.com	jewel@bd.com
Sheet3

Cell Formulas
Range	Formula
C5:C30	C5	=IF(A5:A30=$A$1,B5:B30,"")
Dynamic array formulas.

Hope that helps,

Doug

Hi Doug, Thank you very much for the solution and for the effort you put into the answer, I shall be giving this a try a bit later today! Just a quick question, if it's not too much, say I have further similar information in columns C and D, could these be added into the equation. Obviously the result will have to go in column E. Thank you!

 

[duggie33]

It will likely require a separate formula for column E.

 

[chriseponymous]

Thank you again. Just one last question... when the results are returned there are some rows in the column which have no value. Is there anyway to remove the gaps and have results displayed in sequential rows? Thank you.