The fastest way to find two lists about all is to select them both and click on the conditional formatting -> highlight cells rule -> duplicate values (Excel 2007). The result is that it lists values that are the same. Then one list contains non-highlighted values that are not present in the other list, and vice versa for the other list. I think it’s selling “geeky“, but it gets to work very quickly when you don’t want to mess up.
A Ridiculously easy and fun way to compare 2 lists
[Only Excel 200 works + works, if you are Excel 2003 or earlier, use the technique above]
- (select the first list, then hold the CTRL key and then select the second)Select cells in both l
- Go to Conditional Formatting> Highlight Cell Rule> Duplicate Value
- Press ok
- There is nothing here. go out and play!
excel compare two Columns
2. Compare 2 Columns
Discover how to compare 2 columns. Returns just one formula result with 2 Excel functions
Change some values in this worksheet. You will see that the color of the cell will change depending on whether the value is present in column E.
It is very easy to compare 2 columns in Excel.
Everything is based on whether VLOOKUP finds the value in a specified column.
excel compare two 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)
Then copy the formula down the column. The missing value is immediately visible by the # N / A error value (NA means not applicable).
Compare_2_Columns_1
But displaying # N / A in your workbook is not very attractive
Step 2: Use the ISNA function to perform a test
We will use the ISNA function to optimize the result. Function 2 will return the value:
- True: when the function returns VLOOKUP # N / A
- FALSE: When the function matches
So now we will insert the previous formula into the ISNA function. The result returned by this new formula is TRUE or FALSE, depending on whether the search was successful.
=ISNA(VLOOKUP(A1,$D$1:$D$4,1,0))
Step 3: 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 optimize the results. We can write the following IF function:
=IF(ISNA(VLOOKUP(A1,$D$1:$D$4,1,0)),”Missing”,””)
Change color of missing values
When you have created a logical test (TRUE or FALSE) you can use it as a formula in conditional formatting.
For example, we will reuse the test with the ISNA function and copy it as a rule.
Steps are:
- Select the range of cells where you want to apply your conditional formatting (here A1: A5)
- Open Menu> Conditional Formatting> New Rule
- Use the formula to determine which cells the formula will apply to.
- In the text box, paste the formula that allows us to return TRUE or FALSE (ISNA function)
- Change the formatting by clicking the Format button.
The result is that cells in column A are displayed with a red background if values are not found in column D
Missing-value-in-red
When you compare 2 columns, highlighting them in a different color makes the difference more visible
3. Compare Two Columns For Exact Row Match
This is the simplest form of comparison. In this case, you need to do a row by row comparison and identify which rows have the same data and which are not.
Example: compare cells in the same row
Below is a data set where I need to check whether the name in column A is the same as in column B.
If a match occurs, I need the result as “TRUE”, and if it does not match, I need the result as “FALSE”.
The formula below will do this:
=A2=B2
