Return list after matching multiple criteria

mackensteff

Board Regular
Joined
Feb 9, 2010
Messages
74
Office Version
  1. 365
Platform
  1. MacOS
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:

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
 
Upvote 0
Thanks Dan, you are the man!

This looks like it will work, although at my level it seems more like magic.
 
Upvote 0
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
 
Upvote 0
Canapone,

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

Thanks!
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top