Odd Matching Question

[seguin85]

Hi,

I have some data setup similar to this:

Customer	Region	Program	Unique Program	Europe	China	North America	South America
A1	Europe	X1	1	yes	-	yes	-
A1	North America	X1	0	-	-	-	-
A1	Europe	X5	1	yes	yes	yes	yes
A1	China	X5	0	-	-	-	-
A1	North America	X5	0	-	-	-	-
A1	South America	X5	0	-	-	-	-

<tbody>
</tbody>

Customer, Region and Program are all from a data source and can't be altered. I added the unique program to identify if the program is unique and it works like it should.

What I need it on the unique program row is to identify if that program is in that particular country.

Any ideas?

 

[Eric W]

Perhaps:

	A	B	C	D	E	F	G	H
1	Customer	Region	Program	Unique Program	Europe	China	North America	South America
2	A1	Europe	X1	1	Yes	-	Yes	-
3	A1	North America	X1	0	-	-	-	-
4	A1	Europe	X5	1	Yes	Yes	Yes	Yes
5	A1	China	X5	0	-	-	-	-
6	A1	North America	X5	0	-	-	-	-
7	A1	South America	X5	0	-	-	-	-

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula E2 =IF(COUNTIFS($C:$C,$C2,$B:$B,E$1)*$D2,"Yes","-") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Rada1]

Book1
	A	B	C	D	E	F	G	H
1	Customer	Region	Program	Unique Program	Europe	China	North America	South America
2	A1	Europe	X1	1	yes
3	A1	North America	X1	0
4	A1	Europe	X5	1
5	A1	China	X5	0
6	A1	North America	X5	0
7	A1	South America	X5	0
Sheet2

Cell Formulas
Range	Formula
E2	=IF(IF($D2=1,SUMPRODUCT(--($B$2:$B$7=E$1),--($C$2:$C$7=$C2)),0)=1,"yes","-")

Try this. Copy formula down and right.

 

[mopp1]

Assuming table starts in A1, putting the following formula in D2 and dragging down will give TRUEs and FALSEs (TRUE if there is a yes in the country column that matches the region
=INDEX(E2:H2,MATCH(B2,$E$1:$H$1,0))="yes"

if you want 1s and 0s use
=1*(INDEX(E2:H2,MATCH(B2,$E$1:$H$1,0))="yes")

 

[seguin85]

Thanks Eric!

It worked perfect!

 

[Eric W]

Glad we could help!