Conditional Formatting based on unrelated tables

[Nosh10]

Hello!

I have the following tables/columns -  table 1: customer ID and email address; table 2: supplier ID and email address

I would like to show:

If table 1 and table2  have a matching email address AND have a customer ID and supplier ID, show list of email addresses.

Please see below for an example.

Table 1
Customer ID	Email Address
334	aa@gmail.com
	bb@gmail.com
454	cc@gmail.com
122	dd@gmail.com
	ee@gmail.com
678	ff@gmail.com
	gg@gmail.com
	hh@gmail.com
444	ii@gmail.com
876	jj@gmail.com
444	kkgmail.com
Table 2
Supplier ID	Email Address
1113	aa@gmail.com
1112	bb@gmail.com
	cc@gmail.com
2345	dd@gmail.com
1111	ee@gmail.com
1000	ff@gmail.com
1100	gg@gmail.com
	hh@gmail.com
	ii@gmail.com
5678	jj@gmail.com
2000	kkgmail.com
Table 3 - I'm looking for this result (output)	Table 3
aa@gmail.com
dd@gmail.com
ff@gmail.com
jj@gmail.com
kkgmail.com

Thank you so much!

[v-yuta-msft · Accepted solution]

@Nosh10 ,

You can achieve this in query editor, select the Customer ID and Supplier ID columns in both table, then filter the blank rows like below:

 

Then merge the two filtered tables using inner join based on Email Address.

 

Please also refer to the attachment.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.