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))
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.
Want More VLOOKUP Tips?
Steve says
It might have been an idea to show the second list as well, however still a great tip and one l will use in the future
Meni Porat says
Steve is right.
One can’t fully understand the tip unless the other list in the comparison (i.e. D3:E18) is also displayed.
Barbara says
Meni
Thanks for the feedback I have ameneded to show the two lists
Regards
Barbara
val says
it doesn’t work
Tom Dooley says
@Val: It doesn’t work b’coz there’s an error with the formula provided…and you might have just copied and pasted.
The following is the correct formula, and copying and pasting should now work:
=ISNA(VLOOKUP($A3,$D$3:$E$18,1,FALSE))
Instead of the comma (,) after the VLOOKUP, there should have been an opening round bracket “(“.
Barbara says
@Val- Formula amended
@Tom- Thanks for feedback- formula amended
shirley says
If you want people to use this site – then please make the txt darker. You are not using W3 standards and visually limited people can not see the txt. I am not visually handicapped and can just barely read all the txt on this site.
Henry Standingbear says
This is totally bad ass!
Barbara says
Thanks
Glad It Helped You!