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

gordie_3

New Member
Joined
Jan 4, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
Filter example.jpg


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.
Match 1 column example.jpg
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.
Match 2 column example - doesnt work.jpg


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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Are you trying to return results that are NOT in the clean list?

If so, is this what you are after?

Book1
ABCDEFGHIJK
1First NameLast NamePersonal EmailWork EmailPhoneList To Clean AgainstResult
2BobWBob@gmail.comBob@work.com123-456-7890Bob@gmail.comTomTTom@gmail.comTom@work.com123-456-7891
3TomTTom@gmail.comTom@work.com123-456-7891John@work.comJenniferRJennifer@gmail.comJennifer@work.com123-456-7893
4JohnSJohn@gmail.comJohn@work.com123-456-7892Stu@work.comLarryLLarry@gmail.comLarry@work.com123-456-7894
5JenniferRJennifer@gmail.comJennifer@work.com123-456-7893Theresa@work.com
6LarryLLarry@gmail.comLarry@work.com123-456-7894Otherperson@gmail.com
7StuWStu@gmail.comStu@work.com123-456-7895notonlist@gmail.com
8TheresaFTheresa@gmail.comTheresa@work.com123-456-7896moreinfo@work.com
9Test@work.com
10Contact@gmail.com
Sheet1
Cell Formulas
RangeFormula
G2:K4G2=FILTER(A2:E8, (ISNA(XMATCH(C2:C8,F2:F10)))*(ISNA(XMATCH(D2:D8,F2:F10))))
Dynamic array formulas.
 
Upvote 1
Solution
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. :cry:
 
Upvote 0
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. :cry:
Your formula for the 3rd screenshot is looking in column G, which I believe should be column F.
 
Upvote 0
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!
 
Upvote 0
Maybe try it with AND instead of OR:

Cell Formulas
RangeFormula
G2:G8G2=IF(AND(ISNA(MATCH(C2,$F$2:$F$10, 0)),ISNA(MATCH(D2,$F$2:$F$10,0))),"no match","delete")
 
Upvote 1
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! 👏
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top