# Compare two columns with VLOOKUP in Excel

Method 1: with VLOOKUP

In Excel, compare two columns with VLOOKUP to find common values.

In this example, you'll find email addresses that are identical in both columns:

**VLOOKUP**(B4,C$4:C$9,1,FALSE)

*A*

*B*

*C*

*D*

*E*

*F*

*G*

*1*

*2*

*Column 1*

*3*

*Left list*

*Right list*

*In Left list and in Right list*

*4*

*james@example.com*

*rufus@cogitize.com*

*#N/A*

*👈 The formula is here*

*5*

*mary@example.com*

*caesar@platter.com*

*#N/A*

*👇 And dragged down here*

*6*

*mary@pg.com*

*james@ab.com*

*mary@pg.com*

*7*

*elizabeth@example.com*

*mary@pg.com*

*#N/A*

*8*

*ashley@example.com*

*#N/A*

*9*

*james@ab.com*

*james@ab.com*

*10*

## Procedure

- Enter
**=VLOOKUP**in cell**E4**, where you want to start the list of email addresses that are in both columns. - 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 only one column (*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*.

## Result

The final formula is: `=VLOOKUP(B4, C$4:C$9, 1, FALSE)`

The email addresses that are identical in both lists appear in column E. A #N/A error appears on each row for which the email addresses are not available in the *Right list*.

Method 2: with VLOOKUP and IFNA

In Excel, compare two columns with VLOOKUP to find common values, and replace N/A errors by blanks with IFNA.

In this example, you'll find email addresses that are identical in both columns:

**IFNA**(

**VLOOKUP**(B4,C$4:C$9,1,FALSE),"")

*A*

*B*

*C*

*D*

*E*

*F*

*G*

*1*

*2*

*Column 1*

*3*

*Left list*

*Right list*

*In Left list and in Right list*

*4*

*james@example.com*

*rufus@cogitize.com*

*👈 The formula is here*

*5*

*mary@example.com*

*caesar@platter.com*

*👇 And dragged down here*

*6*

*mary@pg.com*

*james@ab.com*

*mary@pg.com*

*7*

*elizabeth@example.com*

*mary@pg.com*

*8*

*ashley@example.com*

*9*

*james@ab.com*

*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 are in both columns. - 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 only one column (*Right list*) as the Lookup value is equal to the Return value in this case. - Enter
**FALSE**to look for an exact match.

- Enter
- Build the IFNA formula around the VLOOKUP formula:
- Enter
**IFNA(**between the**=**and VLOOKUP formula.

The formula looks like this:`=`

**IFNA(**VLOOKUP(B4, C$4, C$9), 1, FALSE) - Enter
**""**right after the VLOOKUP formula.

The formula looks like this:`=IFNA(VLOOKUP(B4, C$4, C$9), 1, FALSE),`

**""**) - Drag the formula down through as many cells as there are values in the
*Left list*.

- Enter

## Result

The final formula is: `=IFNA(VLOOKUP(B4, C$4, C$9), 1, FALSE), "")`

The email addresses that are identical in both lists appear in column E. A blank appears each time an email address from the *Left list* is not in the *Right list*.

Method 3: with FILTER and COUNTIF

In Excel, combine COUNTIF and FILTER to compare two columns and only return the common values.

In this example, you'll find email addresses that are identical in both columns:

**FILTER**(B3:B8,

**COUNTIF**(C3:C8,B3:B8))

*A*

*B*

*C*

*D*

*E*

*F*

*G*

*1*

*2*

*Left list*

*Right list*

*In Left list and in Right list*

*3*

*james@example.com*

*rufus@cogitize.com*

*mary@pg.com*

*4*

*mary@example.com*

*caesar@platter.com*

*james@ab.com*

*5*

*mary@pg.com*

*james@ab.com*

*6*

*elizabeth@example.com*

*mary@pg.com*

*7*

*ashley@example.com*

*8*

*james@ab.com*

*10*

## Procedure

- Build the COUNTIF formula:
- Enter
**=COUNTIF**in cell**E3**, where you want to start the list of email addresses that are in both columns. - Enter the search range
**C3:C8**, which is the data range containing all the email addresses of the*Right list*. - Enter the range
**B3:B8**as the Criterion to check, for each line, if the email address in the*Left list*is in the*Right list*and return 1 if it is.

- Enter
- Build the FILTER formula around the COUNTIF formula:
- Enter
**FILTER(**between the**=**and COUNTIF formula.

The formula looks like this:`=`

**FILTER(**COUNTIF(C3:C8, B3:B8)) - Enter the Search range
**B3:B8**between FILTER( and COUNTIF.

The formula looks like this:`=FILTER(`

**B3:B8**,COUNTIF(C3:C8, B3:B8))

The condition of the FILTER formula is the result of the COUNTIF formula in this case.

- Enter

## Result

The final formula is: `=FILTER(B3:B8, COUNTIF(C3:C8, B3:B8))`

The email addresses that are in both lists appear in column E. The FILTER formula filters the email addresses that are available in the *Left list* but not in the *Right list*.