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.

Read More: Excel formula to compare two columns and return a value 

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.

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

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

Download Practice Workbook

<< Go Back to Columns | Compare | Learn Excel