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

#### nau2002

##### Board Regular
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 B C D 1​ Group South 2​ Period Q2 3​ 4​ Project B \$45 ​ 5​ Project E \$222 ​ 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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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

</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))

</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))),"")}

</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>

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!

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

</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))

</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)),"")}

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

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

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

Stay warm and have a Happy New Year!

Replies
7
Views
260
Replies
5
Views
191
Replies
10
Views
464
Replies
3
Views
290
Replies
1
Views
192

1,203,078
Messages
6,053,402
Members
444,662
Latest member
AaronPMH

### 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