Index Match Help.... or something else?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
792
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!
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,744
Assuming your sheet1 looks something like this:

Book1
ABCDEFG
1OrchestrasMembersRegionGroup
2ADDR1G2
3AHHR2G2
4ACCR3G1
5ACCR3G1
6AAAR1G1
7AEER2G2
8ACCR3G1
9AHHR2G2
10AEER2G2
11ACCR3G1
12ADDR1G2
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top