Forum Discussion

Chloe_Apsey's avatar
Chloe_Apsey
Copper Contributor
May 17, 2022
Solved

Comparing two lists of Email Address and names and pulling the non-matching data to a new list

Hello,   I currently have an original list of participant's email who I contacted to take part in a study. I now have a second list of the emails of the participants who have taken part in the stud...
excel
  • Lorenzo's avatar
    Lorenzo
    May 17, 2022

    Hi Chloe_Apsey 

     

    Assuming Excel 2021 or 365

     

     

    in E2:

    =FILTER(A2:A5, ISNA(XMATCH(A2:A5,C2:C3)))
HansVogelaar's avatar
HansVogelaar
MVP
May 17, 2022

Chloe_Apsey 

Let's say the original list of email addresses is on Sheet1 in E2:E1000, and the list of participating email addresses is on Sheet2 in E2:E500.

If you have Microsoft 365 or Office 2021, enter the following formula in the cell where you want the output to start. There should be sufficient empty cells below it.

 

=FILTER('Sheet1'!E2:E1000,ISERROR(MATCH('Sheet1'!E2:E1000,'Sheet2'!E2:E500,0)),"None")

 

If you have an older version, enter the following formula in a cell in row 2 where you want the output to start, and confirm it with Ctrl+Shift+Enter to turn it into an array formula:

 

=IFERROR(INDEX('Sheet1'!$E$2:$E$1000,SMALL(IF(ISERROR(MATCH('Sheet1'!$E$2:$E$1000,'Sheet2'!$E$2:$E$500,0)),MATCH(ROW('Sheet1'!$E$2:$E$1000),ROW('Sheet1'!$E$2:$E$1000)),""),ROWS($E$2:$E2))),"")

 

Fill down until the formula returns a blank (or even further down, that doesn't matter).

Resources