4 Ways to Compare Two Columns in Excel for Missing Values

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.

Sample dataset containing a list of sold products

APPROACH

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.

Using ISERROR and VLOOKUP functions to compare two columns

  • Thirdly, use the fill handle to drag the formula to the entire column.
  • Finally, you will get the required result as well.

List of sold products identified by TRUE or FALSE

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

APPROACH

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.

Using IF, ISERROR, and VLOOKUP functions to compare two columns

  • 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.

Identifying missing products from the list of sold products

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

APPROACH

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.

Using NOT, ISNUMBER, and MATCH functions to compare two columns

  • 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.

List of sold products identified by TRUE or FALSE

Finally, this procedure is very easy to achieve.

APPROACH

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.

Selecting New rule in Conditional formatting

  • 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.

Selecting Format from New Formatting Rule

  • Next, you will get another dialogue box name Format Cells.
  • Select Fill.
  • Then choose your desired color.
  • Finally, click on OK.

Selecting Background Color from New Format Cells

  • Eventually, you will get the result you wanted.

Identifying missing products using the background color

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.

Selecting Duplicate Values in Conditional formatting

  • Then you will get a dialogue box name Duplicate Values.
  • Select the Duplicate.
  • Afterward, choose your desired text color then press OK.

Choosing text color from Duplicate Values

  • Finally, you will find the result you required.

Comparing two columns to a match using text color

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.

Selecting Duplicate Values in Conditional formatting

  • 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.

Choosing text color from Duplicate Values

  • Consequently, your desired result will appear to you.

Finding duplicates using the text background color

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.

Using the VLOOKUP function to match two columns

  • Afterward, use the fill handle to drag the formula to the entire column.
  • Finally, you will get the desired result as well.

Result of matching two columns and showing the output in the third one

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.

Using the IF function to compare two lists and return differences in Excel

  • 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.

Getting “Found” as result if the data matches otherwise “Not Found” to compare two lists and return differences

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.

Selecting the Remove Duplicates from the Data tab

  • 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.

Clicking OK from the Remove Duplicates window after selecting the necessary data

  • Eventually, you will get the result you wanted to have.

Dataset after removing the duplicates

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

(Visited 44 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo