Index Match Help.... or something else?

[slam]

I'm spectacularly bad at getting Index/Match to work, and I'm not even sure those are the functions I need, so I'll just lay out my problem, and pray you geniuses can save me!

Not actually related to Orchestras, but seems like a good placeholder for what I'm actually working with!

Sheet1 (includes column headers)

• Column B includes a list of Orchestras. There are many duplicates, and 500 rows in total
• Column D includes a list of members of each Orchestra (not actually relevant for formula, but included in explanation to aid understanding)
• Column E includes which Region the member is from (there are 3 different ones)
• Column G includes which Group each member is part of (there are 2 different ones)

A member can be part of multiple Orchestras, but will only ever belong to one Region and one Group.

Sheet2 (includes column headers)

• Column A includes the list of Orchestras from Sheet1, but without any duplicates (60 rows)

On Sheet 2, what I need to do is identify whether a Region/Group combination exists for each Orchestra. 3 Regions * 2 Groups means there are 6 possibilities. Any Orchestra can have 1 to 6 combinations exist for it, but never 0.

• Column B through G represent the 6 combinations. It's in these columns that I need a formula which I'd want to have display a Y or a N to indicate if that combination exists for each Orchestra.

Any help would be greatly appreciated!

 

[jasonb75]

An XL2BB mini-sheet is worth 1000 screen captures (see the link in my signature below this post).

Best guess from the description would be COUNTIFS, in principle it would be something like

=IF(COUNTIFS(Sheet1!$B:$B,$A2,Sheet1!$E:$E,"Region1",Sheet1!$G:$G,"Group1")>0,"Y","N")

It is not clear how the region / group can be identified based on the column in sheet2 so I've used text descriptions for that part.

 

[Eric W]

Assuming your sheet1 looks something like this:

Book1
	A	B	C	D	E	F	G
1		Orchestras		Members	Region		Group
2		A		DD	R1		G2
3		A		HH	R2		G2
4		A		CC	R3		G1
5		A		CC	R3		G1
6		A		AA	R1		G1
7		A		EE	R2		G2
8		A		CC	R3		G1
9		A		HH	R2		G2
10		A		EE	R2		G2
11		A		CC	R3		G1
12		A		DD	R1		G2
13		B		JJ	R2		G2
14		B		CC	R3		G1
15		B		AA	R1		G1
16		B		DD	R1		G2
17		B		DD	R1		G2
18		B		FF	R3		G1
19		B		AA	R1		G1
20		B		CC	R3		G1
21		B		DD	R1		G2
22		B		BB	R2		G1
23		B		JJ	R2		G2
24		B		CC	R3		G1
25		C		FF	R3		G1
Sheet1

your sheet2 can look like this:

Book1
	A	B	C	D	E	F	G
1	Orchestra	R1/G1	R1/G2	R2/G1	R2/G2	R3/G1	R3/G2
2	A	Y	Y	N	Y	Y	N
3	B	Y	Y	Y	Y	Y	N
4	C	Y	N	Y	Y	Y	N
5	D	Y	Y	Y	Y	Y	N
Sheet2

Cell Formulas
Range	Formula
B2:G5	B2	=IF(COUNTIFS(Sheet1!$B:$B,$A2,Sheet1!$E:$E,LEFT(B$1,2),Sheet1!$G:$G,RIGHT(B$1,2)),"Y","N")

As Jason indicated, you'd need to find a way to identify your groups/regions. I just used R1, G1, etc. and pulled them out of the header in the formula, you'd have to adapt that a bit.