Return list after matching multiple criteria

[mackensteff]

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

 

[Danerida]

I couldn't get formulas to do this entirely.

In a Worksheet_Change event use this 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

=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

 

[mackensteff]

Thanks Dan, you are the man!

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

 

[Canapone]

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

 

[mackensteff]

Canapone,

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

Thanks!