Compare two columns with VLOOKUP in Google Sheets
Method 1: with VLOOKUP
In Google Sheets, compare two columns with VLOOKUP to find common values.
In this example, you'll find email addresses that are identical in both columns:
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 Google Sheets, 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:
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.
- 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 IFNA( between the = and VLOOKUP formula.
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 Google Sheets, 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:
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.
- 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 FILTER( between the = and COUNTIF formula.
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.