# Index Match Help.... or something else?

#### slam

##### Well-known Member
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!

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

• 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!

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### jasonb75

##### Well-known Member
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
Excel Formula:
``=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

##### MrExcel MVP
Assuming your sheet1 looks something like this:

Book1
ABCDEFG
1OrchestrasMembersRegionGroup
3AHHR2G2
4ACCR3G1
5ACCR3G1
6AAAR1G1
7AEER2G2
8ACCR3G1
9AHHR2G2
10AEER2G2
11ACCR3G1
13BJJR2G2
14BCCR3G1
15BAAR1G1
16BDDR1G2
17BDDR1G2
18BFFR3G1
19BAAR1G1
20BCCR3G1
21BDDR1G2
22BBBR2G1
23BJJR2G2
24BCCR3G1
25CFFR3G1
Sheet1

your sheet2 can look like this:

Book1
ABCDEFG
1OrchestraR1/G1R1/G2R2/G1R2/G2R3/G1R3/G2
2AYYNYYN
3BYYYYYN
4CYNYYYN
5DYYYYYN
Sheet2
Cell Formulas
RangeFormula
B2:G5B2=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.

Replies
0
Views
128
Replies
3
Views
30
Replies
3
Views
146
Replies
11
Views
198
Replies
0
Views
351

1,127,098
Messages
5,622,674
Members
415,920
Latest member
ExcelNoob28

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back