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

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

• Excel Compare Text in Two Columns (7 Fruitful Ways)
• How to Find Missing Values in a List in Excel (3 Easy Methods)
• Excel Compare Two Lists and Return Differences (7 Ways)
• VLOOKUP Formula to Compare Two Columns in Different Sheets!
• How to Compare Two Excel Sheets to Find Missing Data (7 Ways)

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.

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.

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

• Compare Three Columns in Excel and Return a Value(4 Ways)
• How to Match Multiple Columns in Excel (Easiest 5 ways)
• Macro to Compare Two Columns in Excel and Highlight Differences
• How to Fill Missing Values in Excel (5 Useful Tricks)
• Excel Macro to Compare Two Columns (4 Easy Ways)
• How to Compare 4 Columns in Excel (6 Methods)
• Excel Count Matches in Two Columns (4 Easy Ways)
• How to Deal with Missing Data in Excel (6 Suitable Ways)
• Compare Two Columns in Excel For Finding Differences