Compare two columns to find differences with VLOOKUP in Excel

Method 1: with VLOOKUP, IF and ISNA

In Excel, compare two columns with VLOOKUP to find missing values and combine with ISNA and IF to show the differences.
In this example, we'll find email addresses that are in the Left list but not in the Right list:

Formula in E4
=IF(ISNA(VLOOKUP(B4,C$4:C$9,1,FALSE)),B4,"")
A
B
C
D
E
F
G
1
2
Column 1
3
Left list
Right list
In Left list but not in Right list
4
james@example.com
rufus@cogitize.com
james@example.com
👈 The formula is here
5
mary@example.com
caesar@platter.com
mary@example.com
👇 And dragged down here
6
mary@pg.com
james@ab.com
7
elizabeth@example.com
mary@pg.com
elizabeth@example.com
8
ashley@example.com
ashley@example.com
9
james@ab.com
10
Download as .xlsx
Separator

Procedure

  1. Build the VLOOKUP formula:
    1. Enter =VLOOKUP in cell E4, where you want to see the list of email addresses that are in Left list but not in Right list.
    2. Enter the Lookup value B4, which contains the first email address of the Left list (james@example.com).
    3. Enter the Search range C$4:C$9, which is the data range containing all the email addresses of the Right list.You must add the dollar sign ($) to fix the rows, so when you drag down the formula, VLOOKUP always refers to the same rows.
    4. Enter Column number 1 because the Search range is composed of one column only (Right list) as the Lookup value is equal to the Return value in this case.
    5. Enter FALSE to look for an exact match.
    6. Drag the formula down through as many cells as there are values in the Left list.
  2. Build the ISNA formula around the VLOOKUP formula:
    1. Enter ISNA( between the = and VLOOKUP formula.
      The formula looks like this: =ISNA(VLOOKUP(B4, C$4:C$9 ,1, FALSE))
      The value of the ISNA formula is the result of the VLOOKUP formula in this case.
  3. Build the IF formula around the ISNA formula:
    1. Enter IF( between the = and ISNA formula.
      The formula looks like this: =IF(ISNA(VLOOKUP(B4, C$4:C$9, 1, FALSE))
    2. Enter B4, right after the VLOOKUP formula, that defines the value to return if the Test is TRUE.
      The formula looks like this: =IF(ISNA(VLOOKUP(B4, C$4:C$9, 1, FALSE)), B4
    3. Enter "" that defines the value to return if the Test is FALSE.
      The formula looks like this: =IF(ISNA(VLOOKUP(B4, C$4:C$9 ,1, FALSE)), B4,"")

Result

The final formula is: =IF(ISNA(VLOOKUP(B4, C$4:C$9, 1, FALSE)), B4,"")
Email addresses listed only in the Left list appear in column E. A blank appears each time an email address from the Left list is found in both columns.

Show reference

Method 2: with VLOOKUP, ISNA and FILTER

In Excel, compare two columns with VLOOKUP to find different values, combine with ISNA to find #N/A errors and use FILTER to present results in a tidy column.
In this example, you'll find email addresses that are in the Left list but not in the Right list:

Formula in E4
=FILTER(B4:B9,ISNA(VLOOKUP(B4:B9,C4:C9,1,FALSE)))
A
B
C
D
E
F
G
1
2
Column 1
3
Left list
Right list
In Left list but not in Right list
4
james@example.com
rufus@cogitize.com
james@example.com
5
mary@example.com
caesar@platter.com
mary@example.com
6
mary@pg.com
james@ab.com
elizabeth@example.com
7
elizabeth@example.com
mary@pg.com
ashley@example.com
8
ashley@example.com
9
james@ab.com
10
Download as .xlsx
Separator

Procedure

  1. Build the VLOOKUP formula:
    1. Enter =VLOOKUP in cell E4, where you want to start the list of email addresses that appear in the Left list but not in the Right list.
    2. Enter the range B4:B9 as the Lookup value, which contains the email addresses of the Left list.
    3. Enter the Search range C4:C9, which is the data range containing the email addresses of the Right list.
    4. Enter Column number 1 because the Search range is composed of one column only (Right list) as the Lookup value is equal to the Return value in this case.
    5. Enter FALSE to look for an exact match.
    6. Drag the formula down through as many cells as there are values in the Left list.
  2. Build the ISNA formula around the VLOOKUP formula:
    1. Enter ISNA( between the = and VLOOKUP formula.
      The formula looks like this: =ISNA(VLOOKUP(B4:B9, C4:C9, 1, FALSE))
      The value of the ISNA formula is the result of the VLOOKUP formula in this case.
  3. Build the FILTER formula around the ISNA formula:
    1. Enter FILTER( between the = and ISNA formula.
      The formula looks like this: =FILTER(ISNA(VLOOKUP(B4:B9, C4:C9, 1, FALSE)))
      The condition of the FILTER formula is the result of the ISNA formula in this case.
    2. Enter the search range B3:B8 between FILTER( and ISNA. The formula looks like this: =FILTER(B3:B8, ISNA(VLOOKUP(B4:B9, C4:C9, 1, FALSE)))

Result

The final formula is: =FILTER(B4:B9, ISNA(VLOOKUP(B4:B9, C4:C9, 1, FALSE)))
Email addresses listed only in the Left list appear in column E. FILTER formula filters the email addresses that are in both columns.

Show reference
← PreviousCompare two columns with VLOOKUPNext →Handle VLOOKUP N/A errors with IFNA