Return list after matching multiple criteria

mackensteff

Board Regular
Joined
Feb 9, 2010
Messages
68
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.
Screen_Shot_2014_09_16_at_8_22_29_PM.png

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:

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Danerida

Board Regular
Joined
Aug 25, 2014
Messages
152
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
 

mackensteff

Board Regular
Joined
Feb 9, 2010
Messages
68
Thanks Dan, you are the man!

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

Canapone

Active Member
Joined
May 10, 2007
Messages
463
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

Board Regular
Joined
Feb 9, 2010
Messages
68
Canapone,

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

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

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