Forum Discussion
Conditional Formatting - Check entry from list
I am trying to create conditional formatting from a new list. If I drop 100 (just an example) companies into a column, I am hoping it can cross reference a specific list I already have. If any of the new 100 companies are already on my list, I would love for them to highlight the cells in the new 100 that hit that criteria of already being on my master list.
Mahalo for your help.
craig319 If both your lists are in the same sheet, you could simply select them both columns and choose the CF rule to show duplicates. This will highlight duplicates in both lists, though. If on the other hand, both list are in separate sheets and/or you don't want the names in the existing list to be highlighted as well, you could use a VLOOKUP function inside a CF rule. Both methods are demonstrated in the attached file
3 Replies
craig319 If both your lists are in the same sheet, you could simply select them both columns and choose the CF rule to show duplicates. This will highlight duplicates in both lists, though. If on the other hand, both list are in separate sheets and/or you don't want the names in the existing list to be highlighted as well, you could use a VLOOKUP function inside a CF rule. Both methods are demonstrated in the attached file
You are awesome. This mostly worked perfect with the vlookup.
2 hiccups if you will.1. Not all the identical names were highlighted
Nationstar Mortgage
Nationstar Mortgage (highlighted)
Nationstar Mortgage (highlighted)2. This weird one
Zody ☪ï¸(highlighted, but this isn't in the referenced list).Mahalo sooooo much
craig319 Without a file to check it's difficult to determine what's happening. Could be that there are leading or trailing spaces causing VLOOKUP not to find all the names to highlight. Cannot possibly say why the other one gets highlighted.
