Skip to content
The Chat Archive
deepseek-v3-0324· DeepSeek LMArena Human Preference 140K

I have 2 excel sheets in same file. how do i find those emails that appear in sheet 1,…

  1. User

    I have 2 excel sheets in same file. how do i find those emails that appear in sheet 1, but don't appear in sheet 2?

  2. Assistant

    To find emails that appear in Sheet1 but not in Sheet2 in the same Excel file, you can use one of the following methods:

    Method 1: Using VLOOKUP (Easiest)

    1. Assume:

      • Sheet1 has emails in Column A (e.g., A2:A100).
      • Sheet2 has emails in Column A (e.g., A2:A100).
    2. In Sheet1, add a new column (e.g., Column B) and enter:

      =IF(ISNA(VLOOKUP(A2, Sheet2!A:A, 1, FALSE)), "Not in Sheet2", "")
      
      • This checks if the email in A2 (Sheet1) exists in Sheet2!A:A.
      • If not found, it returns "Not in Sheet2".
    3. Drag the formula down to apply it to all rows.

    4. Filter Column B for "Not in Sheet2" to see the missing emails.


    Method 2: Using Conditional Formatting (Visual)

    1. Select the email column in Sheet1 (e.g., A2:A100).
    2. Go to Home → Conditional Formatting → New Rule.
    3. Choose "Use a formula to determine which cells to format".
    4. Enter:
      =COUNTIF(Sheet2!A:A, A2)=0
      
    5. Set a format (e.g., red fill) and click OK.
      • Emails in Sheet1 that don’t exist in Sheet2 will be highlighted.

    Method 3: Using Power Query (Best for Large Data)

    1. Go to Data → Get Data → From Table/Range (for both sheets).
    2. In Power Query Editor:
      • Select Sheet1 → Home → Merge Queries.
      • Choose Left Anti Join (keeps only rows in Sheet1 that don’t match Sheet2).
      • Select the email columns as key fields.
    3. Click OK → Close & Load to get the unmatched emails.

    Method 4: Using FILTER (Excel 365/2021)

    If you have Excel 365 or 2021, use:

    =FILTER(Sheet1!A2:A100, ISNA(MATCH(Sheet1!A2:A100, Sheet2!A2:A100, 0)))
    

    This returns only emails in Sheet1 that don’t appear in Sheet2.


    Result

    • VLOOKUP → Simple but requires a helper column.
    • Conditional Formatting → Quick visual check.
    • Power Query → Best for automation & large datasets.
    • FILTER → Cleanest (if available in your Excel version).

    Let me know if you need help with any specific method!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.