Excel lookup and conditional functions are essential in finding data and applying criteria to further accommodate conditions or results. Finding data can be tedious, but comparing two or more lists becomes cumbersome and arduous if done manually. You can use the common find feature to quickly find single piece of data, but it cannot be used to identify and highlight multiple pieces of data and on two separate lists.

Consider a circumstance where you have to compare two different lists and you are trying to find the unmatched items between those two lists. Suppose you have employees on Project A and Project B being tracked on two separate lists. The requirement is that both lists have to have either a unique name or a full name for the employee.  You may also use an alternate identifier if the name does not provide the uniqueness.

Problem/Task:

Identify employees in Project A that are not found in Project B.
Note:  See screenshots below for the two lists for Project A and Project B. 

Solution:

Use a combination of the VLOOKUP function and the IF function to identify the employees that are not found from Project A compared with Project B.
Added Task:  Highlight the employee names using conditional formatting

 

 

IF Function Syntax:

=IF (Logical_test,value_if_true,valued_if_false)

VLOOKUP function Syntax:

=VLOOKUP (Lookup_value,table_array,column_index_num,[range_lookup])

The range for List A is A4:G18, List B is J4:O13. However, the VLOOKUP function only needs to look up from column L, where the Full Name begins.

 

Brief Explanation for the VLOOKUP function:

The formula in column G (cell G4) in List A has a “nested” VLOOKUP function inside the IF function. The ISNA function further handles the error when the VLOOKUP cannot find a matching value in List B, and produces an “N/A” result.

The VLOOKUP function in cell G4 of List A looks up the value in cell C4 in the data area of List B, represented by $L$4:$O$13. The $ signs for the cell reference make the reference absolute, so that it does NOT change when copied to the other cells. The FALSE in the last argument in the VLOOKUP function produces an exact match for the lookup of the value in C4.

=IF(ISNA(VLOOKUP(C4,$L$4:$O$13,1,FALSE)),”Not on B”,””)

 

While the IF function can enter a value of “Not on B” using the nested VLOOKUP to determine the value, the conditional formatting feature in Excel highlights the cells in the desired color to make the results stand out.

 

** The IF and VLOOKUP functions are covered in our Excel Level 2 Intermediate course.