How to Compare Two Columns in Excel for Missing Values

Get FREE Advanced Excel Exercises with Solutions!

When you have data in two separate columns, you may need to compare them to discover what information is missing in one and what data is available in both. Comparing things may be done in a variety of ways, depending on what you want to get out of it. This article will teach you to compare two columns in Excel for missing values in simple ways. For your better understanding, we will use a sample dataset containing Employee Name and Attended Office. From this data, we will find missing values which will tell us the names of employees who didn’t attend the office.

Compare Two Columns for Missing Values

There are several ways to compare two columns in Excel for missing values. We will get familiar with them one by one.


1. Joining VLOOKUP and ISERROR to Compare Two Columns in Excel for Missing Values

In our first method, we will see the use of VLOOKUP and ISERROR functions to find missing data.
Steps:

  • First, click on cell D5 and type the following formula as given below.
    =ISERROR(VLOOKUP(B5,$C$5:$C$11,1,0))

Compare Two Columns in Excel for Missing Values with vlookup

  • Now, press the ENTER key.


Here, we are telling Excel to look up the values in the Employee Name one by one in Attended Office. That’s why we used the VLOOKUP function and also used absolute cell reference for the range C5 to C11. ISERROR function will return the value FALSE if the data is present in both columns otherwise TRUE.
Finally, drag down to AutoFill for the rest of the series.


The value TRUE tells us the Employee Name that is missing in the Attended Office.

Read More: Excel formula to compare two columns and return a value 


2. Merging IF, VLOOKUP and ISERROR to Compare Two Columns for Missing Values

In our previous method, we got the missing data as TRUE. What if we want the exact names that are missing? Let’s see, how to do it.
Steps:

  • First, click on cell D5 and type the following formula.
    =IF(ISERROR(VLOOKUP(B5,$C$5:$C$11,1,FALSE)),B5, "")

Compare Two Columns for Missing Values with IF and Vlookup

  • Now, press the ENTER key.

Compare Two Columns for Missing Values with if,vlookup and iserror
Here, we are telling Excel to look up the values in the Employee Name one by one in the Attended Office. That’s why we used the VLOOKUP function and absolute cell reference as we did in method 1. for the range C5 to C11. ISERROR function will return us the value FALSE if the data is present in both columns otherwise TRUE. And IF function is commanding Excel to return the TRUE as the exact name and FALSE as a blank cell.
Then, drag down to AutoFill the series.


3. Inserting MATCH Function to Compare Two Columns in Excel for Missing Values

In this method, we will see the use of the MATCH function in finding missing values.
Steps:

  • First, click on cell D5 and type the following formula as shown in the image.
    =NOT(ISNUMBER(MATCH(B5,$C$5:$C$11,0)))

  • Now, press the ENTER key.

Compare Two Columns for Missing Values using MATCH and Not function
The MATCH function searches for a specified item in a range of cells and then returns the relative position of that item in the range. ISNUMBER is returning if the matched cell is available in the Attended Office and the NOT function tells telling if not available then the command is  TRUE.
After that, fill the rest of the series using AutoFill.


Our missing values are referred to as TRUE.


4. Comparing Two Excel Columns for Missing Values with Conditional Formatting

In our last method, we will see the use of Conditional Formatting to find missing values in Excel.

  • First, select the range B5:C11 then go to the Conditional Formatting in the Home tab and select New Rule as the image shows.

Compare Two Columns for Missing Values using conditional formatting

  • A dialogue box will pop up, and we will select instructions marked in the red border box and click Format as shown in the image below.

Compare Two Columns for Missing Values with conditional formatting

  • Now, we will select Fill, select our preferred color, and click OK.


Finally, our result looks like this.


Download Practice Workbook


Conclusion

These are four different ways to compare two columns with a missing value. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback. Goodbye!


<< Go Back to Columns | Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

2 Comments
  1. Great, thanks!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo