How to Compare Two Columns to Find Missing Value (Unique Value) in Excel

Suppose we have two lists, list A contains all students, list B only contains students who passed exam. So, list A is longer than list B, and students in list B are all included in list A. If we want to find out which student failed the exam, we can though compare the two list and find out the missing value to confirm the name list. Actually, in our work we often meet the situations that we need to compare two lists and find out the missing values. In this tutorial, we will help you to find out missing values via two ways, the first one is by Conditional Formatting function in excel, the second one is by using formula with VLOOKUP function.

Precondition:

Prepare two lists. List A contains all students. List B contains partial of them.

Compare Two Columns 1

Method 1: Compare Two Columns to Find Missing Value by Conditional Formatting


Step 1: Select List A and List B.

Compare Two Columns 2

Step 2: Click Home in ribbon, click Conditional Formatting in Styles group.

Compare Two Columns 3

Step 3: In Conditional Formatting dropdown list, select Highlight Cells Rules->Duplicate Values.

Compare Two Columns 4

Step 4: In Duplicate Values dialog, select Unique in dropdown list. Keep default value in values with dropdown list. Then click OK.

Compare Two Columns 5

Step 5: Verify that unique values are marked with dark red properly.

Compare Two Columns 6

This way can be used in finding unique values from two lists.

Method 2: Compare Two Columns to Find Missing Value by Formula


Insert a new column between List A and List B.

Compare Two Columns 7

Step 1: In B2 enter the formula =ISERROR(VLOOKUP(A2,$C$2:$C$11,1,FALSE)). VLOOLIP function can help us to look up match value from List B. ISERROR function is used for checking whether match value exists or not, it returns TRUE or FALSE.

Compare Two Columns 8

Step 2: Drag the fill handle down. Verify that B4 and B10 are filled with TRUE.

Compare Two Columns 9

So, in List A, Calvin and Peter are students who failed in exam.

Step 3: If you want to show student name in new list directly, you can update formula =IF(ISERROR(VLOOKUP(A2,$C$2:$C$11,1,FALSE)),A2, “”).

Compare Two Columns 10

Step 4: After applying above formula for the following cells, missing value will display in new list directly.

Compare Two Columns 11

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….

 

 

 

Sidebar