# 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:

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

## Procedure

- Build the VLOOKUP formula:
- 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*. - Enter the Lookup value
**B4**, which contains the first email address of the*Left list*(james@example.com). - 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. - 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. - Enter
**FALSE**to look for an exact match. - Drag the formula down through as many cells as there are values in the
*Left list*.

- Enter
- Build the ISNA formula around the VLOOKUP formula:
- 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.

- Enter
- Build the IF formula around the ISNA formula:
- Enter
**IF(**between the**=**and ISNA formula.

The formula looks like this:`=`

**IF(**ISNA(VLOOKUP(B4, C$4:C$9, 1, FALSE)) - 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** - 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,`

**"")**

- Enter

## 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.

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:

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

## Procedure

- Build the VLOOKUP formula:
- 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*. - Enter the range
**B4:B9**as the Lookup value, which contains the email addresses of the*Left list*. - Enter the Search range
**C4:C9**, which is the data range containing the email addresses of the*Right list*. - 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. - Enter
**FALSE**to look for an exact match. - Drag the formula down through as many cells as there are values in the
*Left list*.

- Enter
- Build the ISNA formula around the VLOOKUP formula:
- 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.

- Enter
- Build the FILTER formula around the ISNA formula:
- 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. - 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)))

- Enter

## 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.