Returning multiple values using multiple matching criteria

W_New

I have a table with three columns. The column headers are thus: Customer, Event, and Owner. I want to enter a "Customer" and "Event" value in reference cells and have the formula return a list of all the "Owner" values that match the Customer and Event criteria.

I have a formula that filters on one criteria and returns a list of all matches (F1 is my reference cell):

=IF(ISERROR(INDEX($C$1:$C$6,SMALL(IF($A$1:$A$6=$F$1,ROW($A$1:$A$6)),ROW(1:1)),1)),"",INDEX($C$1:$C$6,SMALL(IF($A$2:$A$6=$F$1,ROW($A$2:$A$6)),ROW(1:1))

I have a formula that filters on two criteria, but only returns the first match (J1 and J2 are my reference cells):

=INDEX(C2:C6,MATCH(1,(A2:A6=J1)*(B2:B6=J2),0),1)

I need help combining the two formulas so that I can both filter on two criteria and return all the matches in a list. I tried adding a second criterion to the IF statement nested in the SMALL function of the first formula, but it doesn't seem to work.

Excel Hero

It is hard to know what you need without seeing the data, but please give this a go.

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

=IFERROR(INDEX(C$1:C$11,SMALL(IF((A$2:A$11=J$1)*(B$2:B$11=J$2),ROW(A$2:A$11)),ROW(1:1))),"")

After confirming the formula you can then copy it downward.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related