Sometimes we need to compare two columns in **Excel** for missing values. In this article, we will show you how to compare two columns in **Excel** for missing values with 4 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.

## 📂 Download Excel File

Download free workbook from here:

## Learn to Compare Two Columns in Excel for Missing Values with These 4 Methods

While writing this article, “Compare Two Columns in **Excel** for Missing Values”, we’ve considered a dataset that contains approximately **4 **columns and **15** rows. In this dataset, we added **Products**, **Sold Products**, and **Missing Products** to make you better understand. But if you want, you can change the entities and put your own values.

**APPROACH**

### 1. Using VLOOKUP and ISERROR Functions

You can use **VLOOKUP **and **ISERROR **functions to compare two columns in **Excel** for missing values. It is not a difficult task. You can do it very easily. You just need to maintain the following steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Initially, select cell
**D6**. - Secondly, write the following function on that cell.

**=ISERROR(VLOOKUP(B6,$C$6:$C$15,1,0))**

**🔨 Formula Breakdown**

Here,

👉 We are instructing** Excel **to look up each value of **Products** in the **Sold Products** individually. For the **C6** to **C15** interval, we utilized the **VLOOKUP** function and **Absolute Cell References**.

👉 **ISERROR **function will provide us **FALSE **if the information is present in the dataset, otherwise it will give **TRUE**.

- Then tap
**Enter**.

- Thirdly, use the
**fill handle**to drag the formula to the entire column. - Finally, you will get the required result as well.

Undoubtedly. This is a simple approach to compare two columns in **Excel** for missing values.

** Read More: ****5 Ways to Compare 3 Columns for Matches in Excel**

**APPROACH**

### 2. Applying IF, VLOOKUP, and ISERROR Functions Combined

Applying **IF**, **VLOOKUP**, and **ISERROR** functions is another way to compare two columns in **Excel** for missing values. You can achieve this very easily if you follow the given steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, you need to select cell
**D6**. - Secondly, just write the following function on that cell.

**=IF(ISERROR(VLOOKUP(B6,$C$6:$C$15,1,FALSE)),B6, “”)**

**🔨 Formula Breakdown**

Here,

👉 We are instructing** Excel **to look up each value of **Products** in the **Sold Products** individually. For the **C6** to **C15** interval, we utilized the **VLOOKUP** function as well as **Absolute Cell References**.

👉 **ISERROR **function will provide us **FALSE **if the information is present in the dataset, otherwise it will give **TRUE**.

👉 **IF **function will give **FALSE **if the date matches exactly, otherwise it will leave the cell blank.

- Press
**Enter**.

- Afterward, you need to use the
**fill handle**to drag the formula to the entire column. - Eventually, you will get the desired result as well.

Truly, this is a handy technique to compare two columns in **Excel** for missing values.

** Read More: ****5 Ways to Compare Three Columns Using Vlookup in Excel**

**APPROACH**

### 3. Adopting MATCH Function

Adopting the **MATCH **function is one of the easiest approaches to compare two columns in **Excel** for missing values. Follow the below steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Firstly, you have to select cell
**D6**. - Secondly, you need to write the following function on that cell.

**=NOT(ISNUMBER(MATCH(B6,$C$6:$C$15,0)))**

**🔨 Formula Breakdown**

Here:

👉 The **MATCH** function looks for a specific item in a range of cells, finds it, and then provides the item’s location in the range relative to the search criteria.

👉 If the specified cell is present in **Sold Products**,** ISNUMBER** returns.

👉 The **NOT** function indicates that the statement is **TRUE** if it is not present.

- Then press
**Enter**.

- Belatedly, you have to use the
**fill handle**to drag the formula to the entire column. - Consequently, you will get the result you wanted to see.

Finally, this procedure is very easy to achieve.

**APPROACH**

### 4. Utilizing Conditional Formatting

You can utilize **Conditional Formatting **in **Excel **to compare two columns in **Excel** for missing values. It is also a very simple approach. You need to follow the given steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Initially, select cells from
**B6**to**C15**. - Secondly, go to the
**Home**tab and then select**Conditional Formatting**. - Thirdly, click on the
**New Rule**.

- Then you will get a dialogue box name
**New Formatting Rule**. - Select
**Format only unique or duplicate values**. - Afterward, choose
**unique**for**Format all**. - Then click on
**Format**.

- Next, you will get another dialogue box name
**Format Cells**. - Select
**Fill**. - Then choose your desired color.
- Finally, click on
**OK**.

- Eventually, you will get the result you wanted.

Undoubtedly, this approach is straightforward to achieve.

## How to Compare Two Columns in Excel for Matches in Excel

Sometimes you need to compare two columns in **Excel **for matches. You can do this with ease if you follow the below steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, select cells from
**B5**to**C15**. - Secondly, go to the
**Home**tab and then select**Conditional Formatting**. - Thirdly, click on the
**Duplicate Values**.

- Then you will get a dialogue box name
**Duplicate Values**. - Select the
**Duplicate**. - Afterward, choose your desired text color then press
**OK**.

- Finally, you will find the result you required.

Eventually, this procedure is very to achieve.

## How to Find Duplicates in Two Columns in Excel

If you want to find duplicates in **Excel **in two columns then this portion is for you. It is very simple for you. You just need to maintain the following steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Firstly, select cells from
**B6**to**C15**. - Secondly, go to the
**Home**tab and then select**Conditional Formatting**. - Thirdly, click on the
**Duplicate Values**.

- Then you will get a dialogue box name
**Duplicate Values**. - Belatedly, select
**Duplicate**. - Afterward, you need to choose your desired text color and then press
**OK**.

- Consequently, your desired result will appear to you.

That’s how you can find duplicates in **Excel **in two columns.

## How to Match Two Columns and Output in Third One in Excel

Sometimes you need to match two columns in **Excel **and show the output on a third cell. Here we selected some products separately to identify the price of those products. Then we will match whether the selected product is in the products list or not. If you want you can do this very easily by maintaining the steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Firstly, you have to select cell
**F6**. - Secondly, you need to write the following function on that cell.

**=VLOOKUP(E6,$B$6:$C$15,2,FALSE)**

- Then tap
**Enter**.

- Afterward, use the
**fill handle**to drag the formula to the entire column. - Finally, you will get the desired result as well.

Indeed, this is a very easy and simple procedure to match two columns in **Excel **and show the output on a third cell.

** Read More: ****2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel**

## How to Compare Two Lists and Return Differences in Excel

If you want to compare two lists and return differences in **Excel **then you are in the right place. We have considered two datasets of **Sold Products (Day 1) **and **Sold Products (Day 2)**. From these datasets, we will match whether they are similar or not. If similar we will get the result as **Found**, otherwise **Not Found**. You need to follow the given steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, you have to select cell
**H6**. - Secondly, write the below formula on that cell.

**=IF(B6=E6,”Found”,”Not Found”)**

- Then tap
**Enter**.

- Belatedly, you have to use the
**fill handle**to drag the formula to the entire column. - Consequently, you will get the result you wanted as well.

Eventually, you can achieve this approach with no difficulty.

** Read More: ****Excel Formula: Compare Two Columns and Return a Value**

## How to Compare Two Columns and Remove Duplicates in Excel

If you want to compare two columns in **Excel **and **remove duplicates** then this part is especially for you. We are going to show you a very easy way to do this. You just need to maintain the following steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- First, you can select any cell you want.
- Secondly, go to the
**Data**tab. - Then click on
**Data Tools**and then choose to**Remove Duplicates**.

- Then you will get a dialogue box name
**Remove Duplicates**. - Thirdly, check on
**My data has headers**. - Then you need to check the
**Columns**. - Finally, click on
**OK**.

- Eventually, you will get the result you wanted to have.

This technique is one of the simplest techniques so far to compare two columns in **Excel **to remove duplicates.

## 📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️ You need to be careful while using the **Excel **functions.

🖊️ You should be conscious when you intend to use **Conditional Formatting **in **Excel**.

## 📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌 Initially, we used **VLOOKUP **and **ISERROR **functions to compare two columns in **Excel** for missing values.

📌 Secondly, we applied **IF**, **VLOOKUP**, and **ISERROR **functions.

📌 Thirdly, we adopted the **MATCH **function.

📌 Fourthly, we utilized **Conditional Formatting **in **Excel**.

📌 Fifthly, we compared two columns in **Excel **for matches.

📌 Sixthly, we tried to find duplicates in **Excel **from two columns.

📌 Afterward, we matched two columns and showed the output on a third cell in **Excel**.

📌 Belatedly, we compared two lists to return differences in **Excel**.

📌 Finally, we compared two columns to remove duplicates.

## Conclusion

We wish that you would be able to compare two columns in **Excel** for missing values using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about **Excel** tactics, we recommend you to visit our website **www.ExcelDen.com**.

## Related Articles

**4 Ways to Compare Three Columns in Excel and Return a Value****How to Count Matches in Two Columns in Excel**