Lookup with multiple results formatted for email address reference

[Gwill1983]

Hi all,

I have a situation where an automated process references my spreadsheet to get an email address and send emails automatically. This all works perfectly with a simple vlookup, but I have now been asked to enable the email to go to multiple contacts so my basic vlookup now doesnt work!

My spreadsheet table is setup with column T being the managers email address. This is found by looking up in the managers email address table below:

Manager Name	Department	Email Address
TEST1	Warehouse	test@gmail.com
TEST2	Warehouse	test@gmail.com
TEST3	Projects	test@gmail.com
TEST4	IT (inc Business Systems)	test@gmail.com

Obviously, if the department is Projects, then it finds the only result and returns it with no issue. If the department is warehouse then it will only return one of the results from this table.

Ideally, I want this lookup up to find all results and seperate them with a ; in order for this to then be pulled in as an email address within my power automate flow.

Can anybody assist with the lookup element of this?

 

[lrobbo314]

Like this?

BookII.xlsm
	A	B	C	D	E	F	G
1	Manager Name	Department	Email Address			Lookup:	Warehouse
2	TEST1	Warehouse	test1@gmail.com			Result:	test1@gmail.com;test2@gmail.com
3	TEST2	Warehouse	test2@gmail.com
4	TEST3	Projects	test3@gmail.com
5	TEST4	IT (inc Business Systems)	test4@gmail.com
Sheet8

Cell Formulas
Range	Formula
G2	G2	=TEXTJOIN(";",,FILTER(C2:C5,B2:B5=G1))

 

[Gwill1983]

Like this?

BookII.xlsm
	A	B	C	D	E	F	G
1	Manager Name	Department	Email Address			Lookup:	Warehouse
2	TEST1	Warehouse	test1@gmail.com			Result:	test1@gmail.com;test2@gmail.com
3	TEST2	Warehouse	test2@gmail.com
4	TEST3	Projects	test3@gmail.com
5	TEST4	IT (inc Business Systems)	test4@gmail.com
Sheet8

Cell Formulas
Range	Formula
G2	G2	=TEXTJOIN(";",,FILTER(C2:C5,B2:B5=G1))

Thanks for this. I went with a textjoin with a nested if, but wouldn’t have got there without your help.

Thanks