Table of Contents
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.
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).
Then, copy the formula. The missing value is immediately visible by the #N/A error value (NA means 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.
For example here, we will reuse the test with the ISNA function and copy it as a rule.
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 ...
And 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 😊😉