«

»

Compare 2 columns

How to compare 2 columns in Excel?  It's very simple 😄😎👍

I will show you in this post how to do this with the functions

  • VLOOKUP for the research
  • ISNA for the test
  • IF to customize the final result 😉

But first, let's start with a live demo 💛❤💙💜💚

Live demo

In this worksheet (yes, it's a worksheet 😃), change some values. You will see that the color of the cell will change is the value exist or not in column E.

Compare 2 columns

Compare 2 columns it's very easy with Excel.

Everything is based on the fact that VLOOKUP finds a value in a column or not.

If you are not familiar with the VLOOKUP function, you will find all the explanations in this post.

For many Excel users, when the function returns #N/A , this is 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 will be the missing elements between the 2 columns.

Step 1: The VLOOKUP function

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

=VLOOKUP(A1,

Then, we will verify that this value is present in the $D$1 column :$D$4 (do not forget $ to lock 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)

Copy the formula

The missing value is immediately identified by the #N/A error value (Not applicable).

But it is really not good to keep this indication in your workbook😱😱 💔

Step 2: Use the ISNA function for a test

To customize the result, we will use the ISNA function. 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 within an ISNA function. The result returns by this new formula is TRUE or FALSE depending on the fact that the search was a success or not.

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

Step 3: Finish with the IF function

Leaving true or false is still not very visible, 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 color of the missing values

When you have created a logical test (TRUE or FALSE) you can use it as formula 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 column A
  2. Open the menu Home>Conditional Formatting>New Rule.
  3. Choose Use Formula to determine for which cells the format will be applied .
  4. In the text box, paste the formula that allowed us to return TRUE or FALSE (the function ISNA)
  5. Change formatting by clicking the Format button ...

 

comparer_2_colonnes_7And the result in your spreadsheet is cells in red background when the value is not found in the second column 😍😍😍

It is nevertheless more understandable to show the differences by a color rather than to leave a formula 😊😉

 

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/compare-2-columns/


2 comments

  1. Roney Gouveia

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

  2. Jason

    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!

Leave a Reply

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