Compare Two Excel Lists To Highlight Missing Items


A really common task used frequently in Excel is to compare two lists to highlight items that appear in one list but are not in the other. Here is a great way to combine two great Excel features to get exactly the results we need.

We can combine the VLOOKUP function along with Conditional Formatting. The VLOOKUP will be used used to look up an item from one list in the other, and the Conditional formatting will highlight the missing items for us.

In my example I have two lists of products and suppliers and we want to check this list with the new list on the same sheet which has 3 products missing.

 

  • Select the range of cells you wish to format, in my example it is A1:B21
  • Hit Conditional Formatting on the Home Tab
  • Click Use A Formula To Determine Which Cells To Format
  • Enter the VLOOKUP formula below into the field provided

 

=ISNA(VLOOKUP($A3,$D$3:$E$18,1,false))

compare 2 lists1

So, the way this works is the Vlookup is looking for a match in column A. If no match is found the the error #N/A is returned. The ISNA function will return true if a match is not found, hence the cells will be formatted. Nice huh?
In my example I have found the three missing products of my second list stored in cell range D3:E18.

 

 

 

 

 

 

 

 

 

 

 

More Excel Tips

Want More VLOOKUP Tips?

vlookup-book-cover

 

 

 

 

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts