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 💛❤💙💜💚
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
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.
Then, we will verify that this value is present in the $D$1 column :$D$4 (do not forget $ to lock references 😉)
We write the third parameter = 1 (because we have selected only one column in second parameter)
And last parameter = 0 (to perform an exact match).
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.
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:
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.
The steps are
- Select column A
- Open the menu Home>Conditional Formatting>New Rule.
- Choose Use Formula to determine for which cells the format will be applied .
- In the text box, paste the formula that allowed us to return TRUE or FALSE (the function ISNA)
- Change formatting by clicking the Format button ...
It is nevertheless more understandable to show the differences by a color rather than to leave a formula 😊😉