# Match if cell contains value in list

#### alyssa75

##### Board Regular
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!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### VoG

##### Legend
Try

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

Edit: I think I misunderstood the question!

Last edited:

#### DonkeyOte

##### MrExcel MVP
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...

Last edited:

#### Jonmo1

##### MrExcel MVP
try this

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

#### mikerickson

##### MrExcel MVP

=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

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

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

Replies
6
Views
227
Replies
0
Views
29
Replies
8
Views
85
Replies
5
Views
177
Replies
7
Views
288

1,190,897
Messages
5,983,444
Members
439,843
Latest member
PlanetFitness

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back