How to filter a table with two email columns against a list of emails

[gordie_3]

Hello! This sounds so basic, but I swear I've searched for a long time and can't find exactly what I need.

I have a list of (usually thousands of) contacts with multiple columns (E.g., let's simplify to: first name, last name, personal email, work email, phone) that I need to clean against another list of emails. The issue I am having is I need to check if EITHER personal email or work email is a match. I've attempted two approaches that both work on one column, but not two. A fix to check two columns, or an entirely new approach, would be amazing:

1. I've found the formula =FILTER(A2:E8, ISNA(XMATCH(C2:C8,F2:F10)))
works really great to check against ONE column of contact emails and it cleans them out of the resulting table (this is the quickest for my needs of getting a cleaned table), but I haven't been able to modify it to check against both columns (C & D). I've been having to paste the result for C, then repeat the formula for D ( check against personal emails first, then check against work emails separately). I have to repeat this a lot of times for my needs.



2. A formula that pastes the "cleaned" table as it does above is great, but I'd also be open to adding a column that states if there was a match or not, then I could "Sort and Filter" that to copy the cleaned version to another sheet.
E.g. I've found the formula =IFERROR(IF(MATCH(C2,$G$2:$G$10,0),"Yes"),"No") (and then dragging down to check the rest)
works again for checking one column, but then if I have two columns to check against, it gets complicated to filter for yes in one, then yes in the other, then deduplicate the results.

I tried =IF(OR(ISNA(MATCH(C2,$G$2:$G$10, 0)),ISNA(MATCH(D2,$G$2:$G$10,0))),"no match","delete") to check against both, but at the moment this is saying none match, so I'm sure I'm doing this totally wrong.



Anyways, I'm sure one of you have some super obvious solution, so I'd truly appreciate your help! Sorry I can't download the mini-sheet tool!

 

[dreid1011]

Are you trying to return results that are NOT in the clean list?

If so, is this what you are after?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	First Name	Last Name	Personal Email	Work Email	Phone	List To Clean Against	Result
2	Bob	W	Bob@gmail.com	Bob@work.com	123-456-7890	Bob@gmail.com	Tom	T	Tom@gmail.com	Tom@work.com	123-456-7891
3	Tom	T	Tom@gmail.com	Tom@work.com	123-456-7891	John@work.com	Jennifer	R	Jennifer@gmail.com	Jennifer@work.com	123-456-7893
4	John	S	John@gmail.com	John@work.com	123-456-7892	Stu@work.com	Larry	L	Larry@gmail.com	Larry@work.com	123-456-7894
5	Jennifer	R	Jennifer@gmail.com	Jennifer@work.com	123-456-7893	Theresa@work.com
6	Larry	L	Larry@gmail.com	Larry@work.com	123-456-7894	Otherperson@gmail.com
7	Stu	W	Stu@gmail.com	Stu@work.com	123-456-7895	notonlist@gmail.com
8	Theresa	F	Theresa@gmail.com	Theresa@work.com	123-456-7896	moreinfo@work.com
9						Test@work.com
10						Contact@gmail.com
Sheet1

Cell Formulas
Range	Formula
G2:K4	G2	=FILTER(A2:E8, (ISNA(XMATCH(C2:C8,F2:F10)))*(ISNA(XMATCH(D2:D8,F2:F10))))
Dynamic array formulas.

 

[gordie_3]

That does exactly what I need! Thank you so much, that will save me so much time in what I was doing.

Since it might come in handy with some other situations I have, do you also have a solution for the 2nd option, where I was trying to add a column that says if there was a match in either of the columns? My third screenshot shows what I was trying to do, but I know it doesn't work. Trying to figure out when and how you can work with OR statements in excel is very confusing to me.

Seriously, thank you for your help though. I've been doing this the long way for a long time.

 

[dreid1011]

That does exactly what I need! Thank you so much, that will save me so much time in what I was doing.

Since it might come in handy with some other situations I have, do you also have a solution for the 2nd option, where I was trying to add a column that says if there was a match in either of the columns? My third screenshot shows what I was trying to do, but I know it doesn't work. Trying to figure out when and how you can work with OR statements in excel is very confusing to me.

Seriously, thank you for your help though. I've been doing this the long way for a long time.

Your formula for the 3rd screenshot is looking in column G, which I believe should be column F.

 

[gordie_3]

Your formula for the 3rd screenshot is looking in column G, which I believe should be column F.

Oh actually, I just typed the text in the post above incorrectly, but if you zoom in on the screenshot you can see it is referencing F, but it still isn't working. The bad formula I used was: =IF(OR(ISNA(MATCH(C2,$F$2:$F$10, 0)),ISNA(MATCH(D2,$F$2:$F$10,0))),"no match","delete")

But I think I'm just way off the mark anyways. maybe the easier way to put my issue is that the individual checks for:
personal email: =IFERROR(IF(MATCH(C2,$F$2:$F$10,0),"Yes"),"No")
and work email: =IFERROR(IF(MATCH(D2,$F$2:$F$10,0),"Yes"),"No")
Give me the "Yes"/"no" I want. But do you know how to combine that into one formula?

The formula I'm looking for is: If EITHER C2 OR D2 have a match in range F2:F10, return "Yes", otherwise return "No".
(Then I can just drag the formula down to row 3, row 4, etc.)

Thank you so much for your time!

 

[dreid1011]

Maybe try it with AND instead of OR:

Book1
	A	B	C	D	E	F	G
1	First Name	Last Name	Personal Email	Work Email	Phone	List To Clean Against	Result
2	Bob	W	Bob@gmail.com	Bob@work.com	123-456-7890	Bob@gmail.com	delete
3	Tom	T	Tom@gmail.com	Tom@work.com	123-456-7891	John@work.com	no match
4	John	S	John@gmail.com	John@work.com	123-456-7892	Stu@work.com	delete
5	Jennifer	R	Jennifer@gmail.com	Jennifer@work.com	123-456-7893	Theresa@work.com	no match
6	Larry	L	Larry@gmail.com	Larry@work.com	123-456-7894	Otherperson@gmail.com	no match
7	Stu	W	Stu@gmail.com	Stu@work.com	123-456-7895	notonlist@gmail.com	delete
8	Theresa	F	Theresa@gmail.com	Theresa@work.com	123-456-7896	moreinfo@work.com	delete
9						Test@work.com
10						Contact@gmail.com
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=IF(AND(ISNA(MATCH(C2,$F$2:$F$10, 0)),ISNA(MATCH(D2,$F$2:$F$10,0))),"no match","delete")

 

[gordie_3]

PERFECT!

That's so confusing to me because that seems like it should mean if BOTH match then delete, since the "AND" is in there. But you are correct that the AND is actually giving it the instruction of or in this case. I did a few tests and it's looking good.

Thanks so much for all your time today Dreid, you've made my day. Cheers!

 

[dreid1011]

You're welcome, and happy to help.