# How to Compare Two Columns to find Missing Values in Excel – 4 Methods

The sample dataset showcases Employee Name and Attended Office. Find the names of employees who didn’t attend the office.

## Method 1 – Joining the VLOOKUP and the ISERROR Functions to Compare Two Columns in Excel and find Missing Values

Use the VLOOKUP and the ISERROR functions.
Steps:

• Select D5 and enter the following formula.
`=ISERROR(VLOOKUP(B5,\$C\$5:\$C\$11,1,0))`

• Press ENTER.

The VLOOKUP function using an absolute cell referenceÂ  looks up the values in C5:C11. TheÂ ISERROR function will return the FALSE if data is present in both columns. Otherwise, TRUE.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

## Method 2 – Merging the IF, VLOOKUP and ISERROR Functions to Compare Two Columns and Find Missing Values

To see the names that are missing:

• Select D5 and enter the following formula.
`=IF(ISERROR(VLOOKUP(B5,\$C\$5:\$C\$11,1,FALSE)),B5, "")`

• Press ENTER.

The VLOOKUP function and an absolute cell reference are used like in Method 1 for C5:C11. The ISERROR function returns FALSE if data is present in both columns. Otherwise, TRUE. The IF function returns the name for TRUE and blank for FALSE.

• Drag down the Fill Handle to see the result in the rest of the cells.

## Method 3 – Using the MATCH Function to Compare Two Columns in Excel and find Missing Values

Use the MATCH function.
Steps:

• Select D5 and enter the following formula.
`=NOT(ISNUMBER(MATCH(B5,\$C\$5:\$C\$11,0)))`

• Press ENTER.

The MATCH function searches for a specified item in a range and returns its relative position in the range. The ISNUMBERÂ  returns the matched cell when there is a match, and the NOT function returns TRUE when no match is found.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

## Method 4 – Comparing Two Excel Columns with Conditional Formatting

• Select B5:C11.
• Go to Conditional Formatting in the Home tab.
• Select New Rule.

• In the dialog box, select the instructions marked the red in the image below and click Format.

• Select Fill and choose a color.
• Click OK.

This is the output.

<< Go Back to Columns | Compare | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio