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

nau2002

Board Regular
Joined
Dec 19, 2016
Messages
97
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:p></o:p>
B<o:p></o:p>
C<o:p></o:p>
D<o:p></o:p>
1<o:p></o:p>
Group<o:p></o:p>
South<o:p></o:p>
2<o:p></o:p>
Period<o:p></o:p>
Q2<o:p></o:p>
3<o:p></o:p>
4<o:p></o:p>
Project B<o:p></o:p>
$45 <o:p></o:p>
5<o:p></o:p>
Project E<o:p></o:p>
$222 <o:p></o:p>
6<o:p></o:p>
7<o:p></o:p>
Group<o:p></o:p>
Project Name<o:p></o:p>
Q1<o:p></o:p>
Q2<o:p></o:p>
8<o:p></o:p>
North<o:p></o:p>
Project A<o:p></o:p>
400<o:p></o:p>
784<o:p></o:p>
9<o:p></o:p>
South<o:p></o:p>
Project B<o:p></o:p>
500<o:p></o:p>
45<o:p></o:p>
10<o:p></o:p>
West<o:p></o:p>
Project C<o:p></o:p>
509<o:p></o:p>
300<o:p></o:p>
11<o:p></o:p>
North<o:p></o:p>
Project D<o:p></o:p>
212<o:p></o:p>
411<o:p></o:p>
12<o:p></o:p>
South<o:p></o:p>
Project E<o:p></o:p>
858<o:p></o:p>
222<o:p></o:p>
13<o:p></o:p>
North<o:p></o:p>
Project F<o:p></o:p>
900<o:p></o:p>
450<o:p></o:p>
14SouthwestProject G200311
<thead> </thead> <tbody> </tbody>
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
Possibly:

ABCD
1GroupWest
2PeriodQ2
3
4Project C300
5Project G311
6
7GroupProject NameQ1Q2
8NorthProject A400784
9SouthProject B50045
10WestProject C509300
11NorthProject D212411
12SouthProject E858222
13NorthProject F900450
14SouthwestProject G200311

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

Worksheet Formulas
CellFormula
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
CellFormula
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

Board Regular
Joined
Dec 19, 2016
Messages
97
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
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:

ABCD
1Group(s)WestSouthwest
2PeriodQ2
3
4Project C300300
5Project G311311
6
7GroupProject NameQ1Q2
8NorthProject A400784
9SouthProject B50045
10WestProject C509300
11NorthProject D212411
12SouthProject E858222
13NorthProject F900450
14SouthwestProject G200311

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

Worksheet Formulas
CellFormula
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
CellFormula
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

Board Regular
Joined
Dec 19, 2016
Messages
97
Yes, this worked! Your response also helped me to better understand this function. Thanks from MN and Happy New Year!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,427
Messages
5,624,736
Members
416,046
Latest member
Elliottj2121

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