Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two lists. The first has 4000 records of suppliers and the second is a separate list with over 400000 suppliers. Each supplier is identified by name only and there may be different naming conventions.

I have already done the matching where an exact match exists. I then created two further lists;

  1. contains the first word from the 4000 list
  2. contains the first 2 words from the 4000 list

Is there a quick way that I can compare these two new lists to the 400000 list where it will highlight those items in the big list that contain the words in the 2 new lists I have created?

share|improve this question

Assuming your first word list and first 2 words list are one contiguous range called Words in the same book as your big list and your big list is in ColumnA starting at A2:

Select A2, Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format, in Format values where this formula is true: enter:


Format, select your highlighting, OK, OK, Applies to =$A$2:$A$400001, Apply, OK.

For 400,000 names this is not tested.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.