How to Compare Two Columns in Excel for Missing Values

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.

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))

• 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, "")

• Now, press the ENTER key.

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.

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.

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

• 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