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!
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!
