Match if cell contains value in list

[alyssa75]

Hi there!

Is there a formula - perhaps some variation of Match - that I could use to say if this cell "contains" a value in a particular list then return "Yes".

So - if the value in a1 contains any value listed in the list in c1:c5 then return Yes. The trick being that a1 could contain words that are not relevant, but if one matches the list then I want to return the Yes.

Thanks!

 

[VoG]

Try

=IF(ISNUMBER(MATCH(A1,C1:C5,0)),"Yes","No")

Edit: I think I misunderstood the question!

 

[DonkeyOte]

as an array (commit using SHIFT + CTRL + ENTER)

=CHOOSE(ISERROR(FIND(C1:C5,A1))+1,"Yes","No")

Edit, sory, jumped the gun mine won't work either...

 

[Jonmo1]

try this

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(C1:C5,A1)))),"Yes,"No")

 

[mikerickson]

If A1 contains "Smith,Jones,Adams"

=SUMPRODUCT(--(ISNUMBER(FIND(","&C1:C5",", ","&A1&","))))

will be 0 only if none of those names are in C1:C5

(This is untested and may need to be entered as an array formula (Ctrl-Shift-Enter (Cmd+Return for Mac)))

 

[alyssa75]

Thanks gang! This one worked perfect! I LOVE this forum - you folks are fantastic!

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(C1:C5,A1)))),"Yes,"No")