=IF Any 2 Values in A1 Appear in C1, Then B1 ="Yes" (Search, Match, Countif, Index, ?)

[ThanksAlot]

Hi and thanks for considering my scenario.

Column A contains cells with 2 values (letters of the alphabet) in each cell.

The options are: AB, AC, DB, DC.

Column C contains cells with 2-4 values in each cell.

The options are:

AB
AC
ABC
DBC
DA
DC
DAB
DAC
DABC

IF CELL C1 contains any 2 of the values that appear in A1, then B1 = "Yes", otherwise leave cell blank.

I hope this explains what I am attempting to do.

Example: A1 = AB, C1 = AC, B1 = a blank cell.

Example: A1 = DB, C1 = DAB, B1 = "Yes."

Thanks again, Jon

[mehmetcik]

Please Login or Register  to view this content.

[ChemistB]

Try this in B1 copied down

=IFERROR(IF(SEARCH(LEFT(A1,1),C1)*SEARCH(RIGHT(A1,1),C1), "Yes"),"")

[ThanksAlot]

To: mehmetcik and ChemistB,

The formula you each provided was spot on. You both solved my problem. The only difference in the results was that mehmetcik's formula provided for a "yes" when A1 and C1 were empty. ChemistB's formula left B1 empty when A1 and C1 were empty. Thanks for your time and expertise, both. I appreciate your generosity. Thank you. -Jon