Return list after matching multiple criteria

mackensteff

Board Regular
Joined
Feb 9, 2010
Messages
71
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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
71
Office Version
  1. 365
Platform
  1. MacOS
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
71
Office Version
  1. 365
Platform
  1. MacOS
Canapone,

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

Thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,282
Messages
5,836,396
Members
430,425
Latest member
xlsee

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