Jonayed
Sometimes we need to compare two columns in Excel for missing values. In this article, we will show you how to compare two columns in Excel for missing values with 4 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.
📂 Download Excel File
Download free workbook from here:
Learn to Compare Two Columns in Excel for Missing Values with These 4 Methods
While writing this article, “Compare Two Columns in Excel for Missing Values”, we’ve considered a dataset that contains approximately 4 columns and 15 rows. In this dataset, we added Products, Sold Products, and Missing Products to make you better understand. But if you want, you can change the entities and put your own values.
1. Using VLOOKUP and ISERROR Functions
You can use VLOOKUP and ISERROR functions to compare two columns in Excel for missing values. It is not a difficult task. You can do it very easily. You just need to maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell D6.
- Secondly, write the following function on that cell.
=ISERROR(VLOOKUP(B6,$C$6:$C$15,1,0))
🔨 Formula Breakdown
Here,
👉 We are instructing Excel to look up each value of Products in the Sold Products individually. For the C6 to C15 interval, we utilized the VLOOKUP function and Absolute Cell References.
👉 ISERROR function will provide us FALSE if the information is present in the dataset, otherwise it will give TRUE.
- Then tap Enter.
- Thirdly, use the fill handle to drag the formula to the entire column.
- Finally, you will get the required result as well.
Undoubtedly. This is a simple approach to compare two columns in Excel for missing values.
Read More: 5 Ways to Compare 3 Columns for Matches in Excel
2. Applying IF, VLOOKUP, and ISERROR Functions Combined
Applying IF, VLOOKUP, and ISERROR functions is another way to compare two columns in Excel for missing values. You can achieve this very easily if you follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you need to select cell D6.
- Secondly, just write the following function on that cell.
=IF(ISERROR(VLOOKUP(B6,$C$6:$C$15,1,FALSE)),B6, “”)
🔨 Formula Breakdown
Here,
👉 We are instructing Excel to look up each value of Products in the Sold Products individually. For the C6 to C15 interval, we utilized the VLOOKUP function as well as Absolute Cell References.
👉 ISERROR function will provide us FALSE if the information is present in the dataset, otherwise it will give TRUE.
👉 IF function will give FALSE if the date matches exactly, otherwise it will leave the cell blank.
- Press Enter.
- Afterward, you need to use the fill handle to drag the formula to the entire column.
- Eventually, you will get the desired result as well.
Truly, this is a handy technique to compare two columns in Excel for missing values.
Read More: 5 Ways to Compare Three Columns Using Vlookup in Excel
3. Adopting MATCH Function
Adopting the MATCH function is one of the easiest approaches to compare two columns in Excel for missing values. Follow the below steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, you have to select cell D6.
- Secondly, you need to write the following function on that cell.
=NOT(ISNUMBER(MATCH(B6,$C$6:$C$15,0)))
🔨 Formula Breakdown
Here:
👉 The MATCH function looks for a specific item in a range of cells, finds it, and then provides the item’s location in the range relative to the search criteria.
👉 If the specified cell is present in Sold Products, ISNUMBER returns.
👉 The NOT function indicates that the statement is TRUE if it is not present.
- Then press Enter.
- Belatedly, you have to use the fill handle to drag the formula to the entire column.
- Consequently, you will get the result you wanted to see.
Finally, this procedure is very easy to achieve.
4. Utilizing Conditional Formatting
You can utilize Conditional Formatting in Excel to compare two columns in Excel for missing values. It is also a very simple approach. You need to follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cells from B6 to C15.
- Secondly, go to the Home tab and then select Conditional Formatting.
- Thirdly, click on the New Rule.
- Then you will get a dialogue box name New Formatting Rule.
- Select Format only unique or duplicate values.
- Afterward, choose unique for Format all.
- Then click on Format.
- Next, you will get another dialogue box name Format Cells.
- Select Fill.
- Then choose your desired color.
- Finally, click on OK.
- Eventually, you will get the result you wanted.
Undoubtedly, this approach is straightforward to achieve.
How to Compare Two Columns in Excel for Matches in Excel
Sometimes you need to compare two columns in Excel for matches. You can do this with ease if you follow the below steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select cells from B5 to C15.
- Secondly, go to the Home tab and then select Conditional Formatting.
- Thirdly, click on the Duplicate Values.
- Then you will get a dialogue box name Duplicate Values.
- Select the Duplicate.
- Afterward, choose your desired text color then press OK.
- Finally, you will find the result you required.
Eventually, this procedure is very to achieve.
How to Find Duplicates in Two Columns in Excel
If you want to find duplicates in Excel in two columns then this portion is for you. It is very simple for you. You just need to maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cells from B6 to C15.
- Secondly, go to the Home tab and then select Conditional Formatting.
- Thirdly, click on the Duplicate Values.
- Then you will get a dialogue box name Duplicate Values.
- Belatedly, select Duplicate.
- Afterward, you need to choose your desired text color and then press OK.
- Consequently, your desired result will appear to you.
That’s how you can find duplicates in Excel in two columns.
How to Match Two Columns and Output in Third One in Excel
Sometimes you need to match two columns in Excel and show the output on a third cell. Here we selected some products separately to identify the price of those products. Then we will match whether the selected product is in the products list or not. If you want you can do this very easily by maintaining the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, you have to select cell F6.
- Secondly, you need to write the following function on that cell.
=VLOOKUP(E6,$B$6:$C$15,2,FALSE)
- Then tap Enter.
- Afterward, use the fill handle to drag the formula to the entire column.
- Finally, you will get the desired result as well.
Indeed, this is a very easy and simple procedure to match two columns in Excel and show the output on a third cell.
Read More: 2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel
How to Compare Two Lists and Return Differences in Excel
If you want to compare two lists and return differences in Excel then you are in the right place. We have considered two datasets of Sold Products (Day 1) and Sold Products (Day 2). From these datasets, we will match whether they are similar or not. If similar we will get the result as Found, otherwise Not Found. You need to follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you have to select cell H6.
- Secondly, write the below formula on that cell.
=IF(B6=E6,”Found”,”Not Found”)
- Then tap Enter.
- Belatedly, you have to use the fill handle to drag the formula to the entire column.
- Consequently, you will get the result you wanted as well.
Eventually, you can achieve this approach with no difficulty.
Read More: Excel Formula: Compare Two Columns and Return a Value
How to Compare Two Columns and Remove Duplicates in Excel
If you want to compare two columns in Excel and remove duplicates then this part is especially for you. We are going to show you a very easy way to do this. You just need to maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, you can select any cell you want.
- Secondly, go to the Data tab.
- Then click on Data Tools and then choose to Remove Duplicates.
- Then you will get a dialogue box name Remove Duplicates.
- Thirdly, check on My data has headers.
- Then you need to check the Columns.
- Finally, click on OK.
- Eventually, you will get the result you wanted to have.
This technique is one of the simplest techniques so far to compare two columns in Excel to remove duplicates.
📄 Important Notes
You should be aware of the following things while performing the processes mentioned above:
🖊️ You need to be careful while using the Excel functions.
🖊️ You should be conscious when you intend to use Conditional Formatting in Excel.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Initially, we used VLOOKUP and ISERROR functions to compare two columns in Excel for missing values.
📌 Secondly, we applied IF, VLOOKUP, and ISERROR functions.
📌 Thirdly, we adopted the MATCH function.
📌 Fourthly, we utilized Conditional Formatting in Excel.
📌 Fifthly, we compared two columns in Excel for matches.
📌 Sixthly, we tried to find duplicates in Excel from two columns.
📌 Afterward, we matched two columns and showed the output on a third cell in Excel.
📌 Belatedly, we compared two lists to return differences in Excel.
📌 Finally, we compared two columns to remove duplicates.
Conclusion
We wish that you would be able to compare two columns in Excel for missing values using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
Related Articles
- 4 Ways to Compare Three Columns in Excel and Return a Value
- How to Count Matches in Two Columns in Excel
