Match Values in Rows with Partial Values in Columns
 

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).

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

Has anyone here dealt with this kind of issue before?

Cordially,
Ryan---

Match Values in Rows with Partial Values in Columns
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ryguy7272" wrote:
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).

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

Has anyone here dealt with this kind of issue before?

Cordially,
Ryan---

Match Values in Rows with Partial Values in Columns
 

"Max" wrote...
....
Put in B2:
= --ISNUMBER(SEARCH($A2,B$1))
....

Save some keystrokes.

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

Match Values in Rows with Partial Values in Columns
 

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

Thanks so much guys!!
Ryan---



"Harlan Grove" wrote:

"Max" wrote...
....
Put in B2:
= --ISNUMBER(SEARCH($A2,B$1))
....

Save some keystrokes.

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