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.

**Table of Contents**hide

**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 cel**l.

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

Great, thanks!

Dear

Victor,You are most welcome.

Regards

ExcelDemy