CODEWITHSUNDEEP
excelexcel-formula
IdRatherBeGardening
IdRatherBeGardening

Reputation: 65

Excel formula needed: see if a text in one column appears within a text string second column

I need a formula that will search for an exact match to A1 anywhere within the cells of B1:B3, if it finds a match in will a return "yes" on Col C. The match may occur in the beginning, middle or end of a series of words; word series are always comma seperated. The match will never be in the middle of a word iteself. See example:

     A                   B                          C

1    cat dog             red horse, green horse     no
2    cat bird            snake mouse, cat bird      yes
3    cat mouse var dog   cat mouse, cat frog        no

Note: That C3 should returns a "no" since only part of A3 appears in B3.

As a starting point, I'm using:

=IF(ISERROR(VLOOKUP(A1,$B$1:$B$3,1,FALSE)),"no","yes")

But this will only search for an exact match, not a match anywhere in the cell.

Upvotes: 0

Views: 632

Answers (2)

barry houdini
barry houdini

Reputation: 46331

You could also try using COUNTIF with wildcards

=IF(COUNTIF(B$1:B$4,"*"&A1&"*"),"yes","no")

.......but what sort of values are these? You could get some "false positives", e.g. "apple" will match against a cell that reads "four pineapples"

Upvotes: 0

guitarthrower
guitarthrower

Reputation: 5834

Adding some wildcards to your existing formula should do the trick:

=IF(ISERROR(VLOOKUP("*"&A1&"*",$B$1:$B$3,1,FALSE)),"no","yes")

Upvotes: 1

Related Questions