Return Array Where Values Equal Conditional Statements

[mysites]

I'm in need of an Excel formula that returns an array (A long array) where each index value is equal to 1 or 0 - the True/False statement of whether each cell in a range of cells is equal to a value in another range of cells.

I have the following range of cells (This range is really 100 cells long, but shortened for explanation purposes)

Member1

Member2

Member3

Member4

Member5

Member6

Member7

Member8

Member9

Member10

<tbody>
</tbody>

I also have the following mapping table which may be out of numerical/alphabetical order at any time:

Member	Group
Member1	Group 1
Member2	Group 2
Member3	Group 1
Member4	Group 2
Member5	Group 1
Member6	Group 2
Member7	Group 1
Member8	Group 2
Member9	Group 1
Member10	Group 2

<tbody>
</tbody>

I'd like a formula that will output the following array value {1,0,1,0,1,0,1,0,1,0}(For use inside a formula) when fed the mapping value of "Group 1". The array value should be based on the position of the Members in the horizontal range at the top, not based on the mapping table order.

 

[StephenCrump]

=MMULT(--TRANSPOSE(IF(B2:K2=B5:B14,C5:C14="Group 1")),TRANSPOSE(COLUMN(B2:K2)^0))

will return {0;0;0;0;1;0;1;0;0;1} for the example illustrated below:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1
2		Member1	Member2	Member3	Member4	Member5	Member6	Member7	Member8	Member9	Member10
3
4		Member	Group
5		Member7	Group 1
6		Member8	Group 2
7		Member9	Group 2
8		Member10	Group 1
9		Member1	Group 2
10		Member2	Group 2
11		Member3	Group 2
12		Member4	Group 2
13		Member5	Group 1
14		Member6	Group 2
Sheet1

 

[mysites]

Thanks!

 

[StephenCrump]

You're welcome.