How to Compare 2 Columns in Excel

How to Compare 2 Columns in Excel
Last Updated on 01/12/2023
Reading time: 2 minutes

How to compare the contents of 2 columns with Excel?

  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

Logic to Compare 2 columns in Excel

Comparing 2 columns in Excel is very easy. To solve this problem, we will use the VLOOKUP function.

Compare the 2 columns to find the missing data

The trick in this situation is to test if the VLOOKUP functions will returns N/A or not?

  • If the value is missing, the VLOOKUP function will return N/A.
  • Then, we will perform a test on the result N/A

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.
VLOOKUP search the letter A in the second column
  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  🙄🤨

NA means missing values

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

Convert the result as a test

Step 3: Finish with the IF function

Displaying TRUE or FALSE in the cells is not user-friendly. 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","")

The IF function compares 2 columns and returns the missing values

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.

Compare the 2 columns and display the missing values in red

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.

    Reply

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

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

    Reply

  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

    Reply

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

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

    Reply

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

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

    Reply

  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!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Compare 2 Columns in Excel

Reading time: 2 minutes
Last Updated on 01/12/2023

How to compare the contents of 2 columns with Excel?

  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

Logic to Compare 2 columns in Excel

Comparing 2 columns in Excel is very easy. To solve this problem, we will use the VLOOKUP function.

Compare the 2 columns to find the missing data

The trick in this situation is to test if the VLOOKUP functions will returns N/A or not?

  • If the value is missing, the VLOOKUP function will return N/A.
  • Then, we will perform a test on the result N/A

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.
VLOOKUP search the letter A in the second column
  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  🙄🤨

NA means missing values

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

Convert the result as a test

Step 3: Finish with the IF function

Displaying TRUE or FALSE in the cells is not user-friendly. 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","")

The IF function compares 2 columns and returns the missing values

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.

Compare the 2 columns and display the missing values in red

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.

    Reply

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

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

    Reply

  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

    Reply

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

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

    Reply

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

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

    Reply

  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!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *