# Return all Values Matching Another Cell's Value

#### nau2002

##### Board Regular
Hello, 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 8. The number of results to return will vary depending on which 'Group' is selected. Many thanks!
 Group North Period Q1 Project A \$400 Project D \$212 Project F \$900 Group Project Name Q1 Q2 North Project A 400 784 South Project B 500 45 West Project C 509 300 North Project D 212 411 South Project E 858 222 North Project F 900 450

<tbody>
</tbody>

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### CalcSux78

##### Well-known Member

Excel 2010
ABCD
1GroupSouth
2PeriodQ2
3
4Project B\$45
5Project E\$222
6
7GroupProject NameQ1Q2
8NorthProject A400784
9SouthProject B50045
10WestProject C509300
11NorthProject D212411
12SouthProject E858222
13NorthProject F900450
Summary
Cell Formulas
RangeFormula
A4{=IFERROR(INDEX(\$B\$8:\$B\$13,SMALL(IF(\$A\$8:\$A\$13=\$B\$1,ROW(\$C\$8:\$C\$13)),ROW(1:1))-7,1),"")}
A5{=IFERROR(INDEX(\$B\$8:\$B\$13,SMALL(IF(\$A\$8:\$A\$13=\$B\$1,ROW(\$C\$8:\$C\$13)),ROW(2:2))-7,1),"")}
A6{=IFERROR(INDEX(\$B\$8:\$B\$13,SMALL(IF(\$A\$8:\$A\$13=\$B\$1,ROW(\$C\$8:\$C\$13)),ROW(3:3))-7,1),"")}
B4{=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),"")}
B5{=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(2:2))-7,1),"")}
B6{=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(3:3))-7,1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### CalcSux78

##### Well-known Member
p.s. Welcome to the forum...

I think the formulas were cut off due to the length. These are array formulas, so after typing in (or pasting the formula), press [CTRL+SHIFT+ENTER].

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]

​After entering the array formulas in row 4, you can drag those formulas down to row 6.

Last edited:

#### nau2002

##### Board Regular
Big thanks, this worked great!!
I have one other complexity to add...
For one of the Groups, 'West', I also want to include a sub group which is labeled as 'Southwest'. So, when 'West' is the selected Group, the results would include all projects for both 'West' and 'Southwest' (Project G could be 'Southwest' for example). I don't need any type of label or distinguisher on my results as to whether the project came from 'south' or 'southwest'.

Replies
2
Views
338
Replies
1
Views
227
Replies
4
Views
3K
Replies
3
Views
277
Replies
7
Views
2K

1,191,056
Messages
5,984,395
Members
439,884
Latest member
BrownEyedGirl

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