# Return list after matching multiple criteria

#### mackensteff

##### Board Regular
Hello,

I am trying to match two criteria in a table and return a list of matching results. As a picture is worth a thousand words I included the table below. I am trying to match the items in the orange box to the items to the left and then get the results in the green box.

I have tried index and match, but I can only get a single item from that and I would like to return all possible matches.

Any help is appreciated!

Thanks

Last edited:

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I couldn't get formulas to do this entirely.

In a Worksheet_Change event use this code:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, c, arr(), rng As Range, Sex As String, AgeGrp As String
Sex = Cells(2, 7).Value
AgeGrp = Cells(3, 7).Value

Set rng = Cells(1, 3).Resize(Cells(1, 3).End(xlDown).Row)
ReDim arr(rng.Rows.Count, 1)
For Each c In rng
If c.Value = Sex And c.Offset(0, 1).Value = AgeGrp Then
arr(i, 0) = c.Offset(0, -2).Value
i = i + 1
End If
Next c

Cells(2, 8).Resize(UBound(arr)).Value = arr

End Sub``````

Then in Cell I2
Code:
``=IFERROR(INDEX(\$A\$1:\$D\$7,MATCH(H2,\$A\$1:\$A\$7,0),2),"")``
Copy down as far as you need.

Whatever you enter in your orange cells are used as lookup criteria.

Dan

Thanks Dan, you are the man!

This looks like it will work, although at my level it seems more like magic.

Hi,

an attempt: in H2 and to be copied down in H3:h7 and to the left in I2:I7

=IFERROR(INDEX(A\$2:A\$7,AGGREGATE(15,6,ROW(\$A\$2:\$A\$7)-1/((\$C\$2:\$C\$7=\$G\$2)*(\$D\$2:\$D\$7=\$G\$3)),ROW(\$A1))),"")

You need to have Excel 2010 or newer version to use aggregate.

Regards

Canapone,

I have looked at a lot of different methods and yours officially qualifies as elegant. I appreciate it!

Thanks!

Replies
4
Views
165
Replies
9
Views
171
Replies
2
Views
176
Replies
3
Views
468
Replies
23
Views
405

1,218,560
Messages
6,143,202
Members
450,469
Latest member
brent3162

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