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 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 the column otherwise TRUE.
Finally, drag down to AutoFill for the rest of the series.


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

Read More: How to Compare Multiple Columns Using VLOOKUP in Excel (5 Methods)


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 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 Attended Office. That’s why we used the VLOOKUP function and also used 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 the column 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.

Read More: How to Compare 4 Columns in Excel VLOOKUP (Easiest 7 Ways)


Similar Readings


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 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 Attended Office and the NOT function is 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.

Read More: How to Compare Two Columns in Excel for Match (8 ways)


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 the Fill then select our preferred color then click OK.


Finally, our result looks like this.

Read More: Excel Formula to Compare and Return Value from Two Columns (5 Formulas)


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. You may also browse this site’s other Excel-related topics.


Related Articles

Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

2 Comments
  1. Great, thanks!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo