Search Tips and Tricks

Compare 2 Columns

Reading Time: 3 minutes

How to compare the contain of 2 columns? Just one formula with 2 Excel functions returns the result

Compare 2 columns

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

Missing value in a list

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 signifies 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: The VLOOKUP function

Let's start by typing our VLOOKUP function, and the first item you want to search,
in cell B1 . Here we want to find the contents of cell A1.

=VLOOKUP(A1,

Then, we will verify that this value is present in the column $D$1:$D$4 (do not forget $ to lock the cell references 😉)

=VLOOKUP(A1,$D$1:$D$4,

We write the third parameter = 1 (because we have selected only one column in second parameter).

=VLOOKUP(A1,$D$1:$D$4,1,

And last parameter = 0 (to perform an exact match).

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

Function VLOOKUP to return the same value in the other column

Then copy the formula down the column. The missing value is immediately visible by the #N/A error value (NA means Not Applicable).

When the value is not found, the VLOOKUP function returns N/A

But it is not very visually appealing to display #N/A in your workbook  🙄🤨

Step 2: Use the ISNA function to perform a test

We will use the ISNA function to customize the result. The function will return 2 values:

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

So now we will insert the previous formula into an ISNA function. The result returned by this new formula is TRUE or FALSE depending whether or not the search was successful.

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

Transform the VLOOKUP function with the function ISNA

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

The test returns the result expected

Change color of the missing values

When you have created a logical test (TRUE or FALSE) you can use it as custom rule in a conditional formatting.

For example here, we will reuse the test with the ISNA function and copy it as a rule.

The steps are:

  1. Select the range of cells where you want to apply your conditional formatting (here A1:A5)
  2. Open the menu Home>Conditional Formatting>New Rule
  3. Choose Use Formula to determine which cells the format will be applied to.
  4. In the text box, paste the formula that allowed us to return TRUE or FALSE (the ISNA function)
  5. Change formatting by clicking the Format button.
Copy of the formula as conditional formatting rules

The result is that cells in column A are displayed with a red background when the value is not found in the column D 😍😍😍

Missing value are in red automatically

It is more visible to show the differences by a highlighting them in a different color when you compare 2 columns 😉

Tutorial video

In this video, you will turn up all the steps

Related posts

Convert list to Double-Entry Table

Frédéric LE GUEN

Dash for 0 in Excel

Frédéric LE GUEN

Calculation without equal

Frédéric LE GUEN

6 comments

Palani 09/09/2020 at 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
Subbu 21/02/2020 at 00:20

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

Reply
rajesh 23/10/2019 at 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
Sterling 13/03/2019 at 22:14

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

Reply
Roney Gouveia 23/08/2018 at 23:14

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

Reply
Jason 10/07/2018 at 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 Comment