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

Formula in E4
=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
Separator

## Procedure

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

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

Formula in E4
=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
Separator

## Procedure

1. Build the VLOOKUP formula:
1. Enter =VLOOKUP in cell E4, where you want to start the list of email addresses that are in both columns.
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 only one column (Right list) as the Lookup value is equal to the Return value in this case.
5. Enter FALSE to look for an exact match.
2. Build the IFNA formula around the VLOOKUP formula:
1. Enter IFNA( between the = and VLOOKUP formula.
The formula looks like this: `=IFNA(VLOOKUP(B4, C\$4, C\$9), 1, FALSE)`
2. Enter "" right after the VLOOKUP formula.
The formula looks like this: `=IFNA(VLOOKUP(B4, C\$4, C\$9), 1, FALSE), "")`
3. Drag the formula down through as many cells as there are values in the Left list.

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

Formula in E3
=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
Separator

## Procedure

1. Build the COUNTIF formula:
1. Enter =COUNTIF in cell E3, where you want to start the list of email addresses that are in both columns.
2. Enter the search range C3:C8, which is the data range containing all the email addresses of the Right list.
3. 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.
2. Build the FILTER formula around the COUNTIF formula:
1. Enter FILTER( between the = and COUNTIF formula.
The formula looks like this: `=FILTER(COUNTIF(C3:C8, B3:B8))`
2. 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.

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