Match Values in Rows with Partial Values in Columns

[ryguy7272]

I am trying to match values such as A, B, C, and D, all in rows, with
corresponding values, all in columns. The columns looks like the following:
A A B B B C A B C A C C C D A C D A B C
D...you get the idea. There are 15 columns of combinations of A, B, C, and D.

I am trying to place a “1†in the cell where the value in the columns match
the values in the rows and a “0†where there are no matches. For instance,
in the cell that references both A and A, I would like to have a "1". In a
cell that references both A and A B, I would like to have a "1", which tells
me there is at least a partial match. If I reference a cell with both B and
A B, I would like to have a "1" (again, there is a partial match here).

I’m sure it requires some kind of IF function. It may require a CSE
function. I tried combinations of VLOOKUP and SUMPRODUCT and MATCH; didn’t
achieve desired results yet.

Has anyone here dealt with this kind of issue before?

Cordially,
Ryan---

 

[Max]

Assume lookup values in A2 down, col headers in B1 across

Put in B2:
= --ISNUMBER(SEARCH($A2,B$1))
Copy across/fill down to populate the grid

Replace SEARCH with FIND if you need it to be a stricter case sensitive
search. SEARCH is not case sensitive.

 

[Harlan Grove]

Put in B2:
= --ISNUMBER(SEARCH($A2,B$1))

....

Save some keystrokes.

=COUNT(SEARCH($A2,B$1))

 

[ryguy7272]

Wow! You guys are amazing! Both functions worked excellent. I noticed that
blanks resulted in 1’s, so I modified Harlan’s function and went with the
following: =IF($A2="",0,COUNT(SEARCH($A2,B$1)))

Thanks so much guys!!
Ryan---