Return all Cells Matching Another Cell's Value + 1 other factor

[nau2002]

Hello! I originally posted to this forum and got a response on how to do the following:

"I'm trying to find a formula that will return a list of all cells matching the value of another cell. The 'Group' and 'Period' values in the first two rows are being selected via drop down menu.
So the result should be something like what begins in cell A4 and B4 in my table below. The 'Source' Data looks like what is shown in the data section beginning on row 7. The number of results to return will vary depending on which 'Group' is selected."

Someone kindly responded with the following formulas which worked great:
A4 formula
=IFERROR(INDEX($B$8:$B$13,SMALL(IF($A$8:$A$13=$B$1,ROW($C$8:$C$13)),ROW(1:1))-7,1),"") Press [CTRL+SHIFT+ENTER]

B4 formula
=IFERROR(INDEX(OFFSET($B$8:$B$13,0,MATCH($B$2,$C$7:$D$7,0)),SMALL(IF($A$8:$A$13=$B$1,ROW($C$8:$C$13)),ROW(1:1))-7,1),"") Press [CTRL+SHIFT+ENTER]

However, I then found out about a new requirement where the 'West' Group should include results matching both 'West' and 'Southwest'. Whether it is 'Southwest' versus 'West' doesn't need to be indicated anywhere in the list, but all projects need to be included in the results. Any suggestions on how I could modify the formulas above to get there? Any tips are much appreciated!

	A<o></o>	B<o></o>	C<o></o>	D<o></o>
1<o></o>​	Group<o></o>	South<o></o>
2<o></o>​	Period<o></o>	Q2<o></o>
3<o></o>​
4<o></o>​	Project B<o></o>	$45 <o></o>​
5<o></o>​	Project E<o></o>	$222 <o></o>​
6<o></o>​
7<o></o>​	Group<o></o>	Project Name<o></o>	Q1<o></o>	Q2<o></o>
8<o></o>​	North<o></o>	Project A<o></o>	400<o></o>​	784<o></o>​
9<o></o>​	South<o></o>	Project B<o></o>	500<o></o>​	45<o></o>​
10<o></o>​	West<o></o>	Project C<o></o>	509<o></o>​	300<o></o>​
11<o></o>​	North<o></o>	Project D<o></o>	212<o></o>​	411<o></o>​
12<o></o>​	South<o></o>	Project E<o></o>	858<o></o>​	222<o></o>​
13<o></o>​	North<o></o>	Project F<o></o>	900<o></o>​	450<o></o>​
14	Southwest	Project G	200	311

<thead> </thead> <tbody> </tbody>

 

[Eric W]

Possibly:

	A	B	C	D
1	Group	West
2	Period	Q2
3
4	Project C	300
5	Project G	311
6
7	Group	Project Name	Q1	Q2
8	North	Project A	400	784
9	South	Project B	500	45
10	West	Project C	509	300
11	North	Project D	212	411
12	South	Project E	858	222
13	North	Project F	900	450
14	Southwest	Project G	200	311

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

Sheet3​

Worksheet Formulas Cell Formula B4 =IF(A4="","",SUMIFS(OFFSET($C$8:$C$14,0,MATCH($B$2,$C$7:$D$7,0)-1),$A$8:$A$14,"*"&$B$1&"*",$B$8:$B$14,A4)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A4 {=IFERROR(INDEX($B$8:$B$14,SMALL(IF(ISNUMBER(SEARCH($B$1,$A$8:$A$14)),ROW($A$8:$A$14)-ROW($A$8)+1),ROWS($A$4:$A4))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[nau2002]

This almost worked for me. It worked given the example that I illustrated for you. Problem is when I went to apply this to my actual data, it doesn't work as the name 'West' isn't contained in the secondary group. My apologies, I didn't think of that when I came up with an example.
So basically, it would be same example as above but the secondary group to be included with 'West' would be named something like 'Other'. Is there a way to modify the formulas you created to look for one name and then the other?

Thanks for your time and effort!

 

[Eric W]

In that case, I'd list all the groups you want to include instead of trying to look for a substring in the name. For example:

	A	B	C	D
1	Group(s)	West	Southwest
2	Period	Q2
3
4	Project C	300	300
5	Project G	311	311
6
7	Group	Project Name	Q1	Q2
8	North	Project A	400	784
9	South	Project B	500	45
10	West	Project C	509	300
11	North	Project D	212	411
12	South	Project E	858	222
13	North	Project F	900	450
14	Southwest	Project G	200	311

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

Sheet7​

Worksheet Formulas Cell Formula B4 =IF(A4="","",SUMIFS(OFFSET($C$8:$C$14,0,MATCH($B$2,$C$7:$D$7,0)-1),$B$8:$B$14,A4)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A4 {=IFERROR(INDEX($B$8:$B$14,SMALL(IF(ISNUMBER(MATCH($A$8:$A$14,$B$1:$E$1,0)),ROW($A$8:$A$14)-ROW($A$8)+1),ROWS($A$4:$A4))),"")} C4 {=IFERROR(INDEX($C$8:$D$14,SMALL(IF(ISNUMBER(MATCH($A$8:$A$14,$B$1:$E$1,0)),ROW($A$8:$A$14)-ROW($A$8)+1),ROWS($A$4:$A4)),MATCH($B$2,$C$7:$D$7,0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

In B1, C1, (and up to E1 in this example) put the groups you want to have returned. The formula in B4 is a bit simpler, but it expects that the project names are unique. If that's not the case, use the array formula in C4.

Let me know how this works!

 

[nau2002]

Yes, this worked! Your response also helped me to better understand this function. Thanks from MN and Happy New Year!

 

[Eric W]

Glad it works for you!

I visited MN in January a few years back. I'm still trying to warm up!

Stay warm and have a Happy New Year!