0

I have two tables (more like lists) in an excel doc; both are only one column. I want to create a formula that will search the first list and highlight any cells that contain one of the words in the second list.

For example:

List 1:
dry shampoo
nail polish
leave in conditioner
hand lotion
face mask

List 2:
mask
shampoo
soap
moisturizer
cleanser
conditioner

The formula would search List 1 and highlight the cells "dry shampoo" "leave in conditioner" and "face mask" based on the criteria that the words shampoo, conditioner, and mask are in List 2.

Anyone know if this is possible?

Rachel
  • 21
  • 2
  • 1
    Hi, welcome to SO. Always good to show some reference were you might have looked first. Try reading up in https://www.excel-university.com/vlookup-and-wildcards/ – indofraiser Nov 01 '18 at 22:14

1 Answers1

11

Yup:

=SUMPRODUCT(--ISNUMBER(SEARCH($C$1:$C$6,A1)))>0

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • That is beautiful. My only regret is that I have but one upvote to give. – JNevill Nov 01 '18 at 21:30
  • @JNevill no worries, got you covered there. – BigBen Nov 01 '18 at 21:31
  • @Scott Craner: I put this '{=SEARCH($C$1:$C$6,A1)}' into the 'D' Column and all results were '#VALUE!' except for the last (5th) record (face mask) the result being 6, correctly showing it found mask on the 6th position. Any Ideas why it didn't show the positions for the 1st and 3rd records? – VBasic2008 Nov 02 '18 at 01:39
  • @vbasic2008 that is why we need the rest it does not work on it's own. – Scott Craner Nov 02 '18 at 01:57