MS Office Tips: Compare Two Lists to Highlight Missing Items

It is a common task in Excel to have to compare two lists and highlight those items that appear in one list and not on the other.

To do this we can use the Vlookup function within the Conditional Formatting tool. The Vlookup function can be used to lookup an item from one list in the other. The Conditional Formatting tool can then be used to highlight the missing items.

The example below shows a list of members. We want to check this list against the same list (minus 2 records) on another sheet.

1. Select the range of cells you want to format. In the example above this would be A2:D21.

2. Click the Conditional Formatting button on the Home tab of the Ribbon

3. Select New Rule from the menu

4. Click Use a formula to determine which cells to format

5. Enter the formula below into the field provided

The Vlookup function is looking for a match in column A. If there is a match then the ID is returned. If not the error #N/A is returned. The ISNA function will return true if a match is not found, meaning the cells will be formatted (You can also compare two lists for matched items using the Match function).

=ISNA(VLOOKUP($A2,'Members 2'!$A$2:$A$19,1,FALSE))