«

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, we will be able to identify the missing elements between the two 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 finish to write the function with the third parameter = 1 (because we have selected only one column in second parameter) and last parameter to 0 (to perform an exact match).

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

comparer_2_colonnes_3

Copy the formula, we obtain the following result (the missing value is the letter c)

 

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 😊😉

 

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js

Leave a Reply

%d bloggers like this: