# How to Compare 2 Columns in Excel

#### Frédéric LE GUEN

Last Updated on 01/09/2023
Reading time: 3 minutes

How to compare the contents of 2 columns with Excel? In one formula Excel will return the result

1. Write a VLOOKUP functions

The VLOOKUP function will help you to find the missing values

2. Use the ISNA function

This function will help to convert the previous result as a test

3. Write an IF function

This function will be useful to customize the final result

## Compare 2 columns

Comparing 2 columns in Excel is very easy. Everything is based on whether or not VLOOKUP finds a value in a specified column.

If you are not familiar with the VLOOKUP function, please read this article. Many Excel users believe that when the function returns #N/A it means an error. Not at all! #N/A means "I did not find the item you were looking for". In the case of a comparison between two columns, it is the value #N/A that will interest us. Thus, the "error" indicates where an element does not exist in both columns.

## Step 1: Write a VLOOKUP function

Let's start by writing a VLOOKUP function to find, or not, an item in the second column.

=VLOOKUP(A1,\$D\$1:\$D\$4,1,0)

1. First, we want to find the value in A1 is in the second column
2. Then, we will verify if this value is present in the column \$D\$1:\$D\$4 (do not forget the \$ to lock the cell references)
3. The third parameter = 1 because we have selected only one column in the second argument.
4. And last parameter = 0 to perform an exact match.
1. Then copy the formula down the column.

The missing value is immediately visible by the #N/A error value (NA means Not Applicable). But it is not very visually appealing to display #N/A in your workbook  🙄🤨

## Step 2: Use the ISNA function to perform a test

The ISNA function returns TRUE or FALSE

• TRUE: when the function VLOOKUP returns #N/A
• FALSE: when the function has matched

So, with this function, instead of returning N/A, we will return TRUE when the lookup failed.

=ISNA(VLOOKUP(A1,\$D\$1:\$D\$4,1,0))

## Step 3: Finish with the IF function

Displaying TRUE or FALSE is still not very aesthetically appealing, especially if you have to present this document to other people. This is why we will integrate this test into an IF function to customize the result. We can write the following IF function:

=IF(ISNA(VLOOKUP(A1,\$D\$1:\$D\$4,1,0)),"Missing","")

## Change the color of the missing values

If you prefer to change the color, you can insert the logical test with ISNA into a custom rule in conditional formatting. Like that, all the missing values will be displayed with a custom color.

You can also use this trick to highlight missing values in both columns and without formula.

## Tutorial video

In this video, you will turn up all the steps to compare two columns with Excel

#### 6 Comments

1. Palani
09/09/2020 @ 13:49

This article is very helpful and saved a lot a time.. The article is pretty easy to follow. Looks like beginners can follow and achieve what they need.

Thanks for publishing such a good one.

2. Subbu
21/02/2020 @ 00:20

It's so helpful. Thank you very much..!!

3. rajesh
23/10/2019 @ 14:16

Thank you very much.

how to compare values with multiple columns; how do i put that formula, any help in this regard is highly appreciated

4. Sterling
13/03/2019 @ 22:14

Beautiful. Did what I needed after a numerous searches. Thanks!

5. Roney Gouveia
23/08/2018 @ 23:14

Thank you! It helped me a lot and made my work so much faster!

6. Jason
10/07/2018 @ 16:03

Awesome, this is going to make a lot of what I do much easier. Comes up a lot in IT where I have to match up two lists that match on a particular column, say computer hostnames, so I can pair information from the first list to info in the second list. Wish I'd known about this function years ago!

# How to Compare 2 Columns in Excel

Reading time: 3 minutes
Last Updated on 01/09/2023

How to compare the contents of 2 columns with Excel? In one formula Excel will return the result

1. Write a VLOOKUP functions

The VLOOKUP function will help you to find the missing values

2. Use the ISNA function

This function will help to convert the previous result as a test

3. Write an IF function

This function will be useful to customize the final result

## Compare 2 columns

Comparing 2 columns in Excel is very easy. Everything is based on whether or not VLOOKUP finds a value in a specified column.

If you are not familiar with the VLOOKUP function, please read this article. Many Excel users believe that when the function returns #N/A it means an error. Not at all! #N/A means "I did not find the item you were looking for". In the case of a comparison between two columns, it is the value #N/A that will interest us. Thus, the "error" indicates where an element does not exist in both columns.

## Step 1: Write a VLOOKUP function

Let's start by writing a VLOOKUP function to find, or not, an item in the second column.

=VLOOKUP(A1,\$D\$1:\$D\$4,1,0)

1. First, we want to find the value in A1 is in the second column
2. Then, we will verify if this value is present in the column \$D\$1:\$D\$4 (do not forget the \$ to lock the cell references)
3. The third parameter = 1 because we have selected only one column in the second argument.
4. And last parameter = 0 to perform an exact match.
1. Then copy the formula down the column.

The missing value is immediately visible by the #N/A error value (NA means Not Applicable). But it is not very visually appealing to display #N/A in your workbook  🙄🤨

## Step 2: Use the ISNA function to perform a test

The ISNA function returns TRUE or FALSE

• TRUE: when the function VLOOKUP returns #N/A
• FALSE: when the function has matched

So, with this function, instead of returning N/A, we will return TRUE when the lookup failed.

=ISNA(VLOOKUP(A1,\$D\$1:\$D\$4,1,0))

## Step 3: Finish with the IF function

Displaying TRUE or FALSE is still not very aesthetically appealing, especially if you have to present this document to other people. This is why we will integrate this test into an IF function to customize the result. We can write the following IF function:

=IF(ISNA(VLOOKUP(A1,\$D\$1:\$D\$4,1,0)),"Missing","")

## Change the color of the missing values

If you prefer to change the color, you can insert the logical test with ISNA into a custom rule in conditional formatting. Like that, all the missing values will be displayed with a custom color.

You can also use this trick to highlight missing values in both columns and without formula.

## Tutorial video

In this video, you will turn up all the steps to compare two columns with Excel

#### 6 Comments

1. Palani
09/09/2020 @ 13:49

This article is very helpful and saved a lot a time.. The article is pretty easy to follow. Looks like beginners can follow and achieve what they need.

Thanks for publishing such a good one.

2. Subbu
21/02/2020 @ 00:20

It's so helpful. Thank you very much..!!

3. rajesh
23/10/2019 @ 14:16

Thank you very much.

how to compare values with multiple columns; how do i put that formula, any help in this regard is highly appreciated

4. Sterling
13/03/2019 @ 22:14

Beautiful. Did what I needed after a numerous searches. Thanks!

5. Roney Gouveia
23/08/2018 @ 23:14

Thank you! It helped me a lot and made my work so much faster!

6. Jason
10/07/2018 @ 16:03

Awesome, this is going to make a lot of what I do much easier. Comes up a lot in IT where I have to match up two lists that match on a particular column, say computer hostnames, so I can pair information from the first list to info in the second list. Wish I'd known about this function years ago!

{"mobile_concate":{"mobile-menu":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/mobile-menu.min.css"},"tf_base":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/base.min.css","v":"7.3.1"},"themify_common":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/themify.common.min.css","v":"7.3.1"},"builder-styles-css":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/themify-builder-style.min.css","v":"7.3.1"},"tb_tbp":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/tbp-style.min.css","v":"3.1.2"},"tf_theme_site-logo":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-logo.min.css","v":"7.1.4"},"tf_theme_site-description":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-description.min.css","v":"7.1.4"},"tf_search_form":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/modules\/search-form.min.css","v":"7.3.1"},"tb_featured-image":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/image.min.css","v":"7.3.1"},"tb_divider":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/divider.min.css","v":"7.3.1"},"tb_post-content":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.min.css","v":"7.3.1"},"wp-block-library":{"s":"https:\/\/www.excel-exercise.com\/wp-includes\/css\/dist\/block-library\/style.min.css","v":"6.3.1"},"tb_comments":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/modules\/comments.min.css","v":"3.1.2"},"tb_text":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.min.css","v":"7.3.1"},"theme-style":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/style.min.css","v":"7.1.4"},"themify-media-queries":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/media-queries.min.css","v":"7.1.4","m":"(max-width:1200px)"}}