# Return Instances where two criteria are met

on
Is there a way (array formula, row() function, etc.) to return the instances of, say, a club where two criteria are met.  For example, if I have a list of clubs, each of which is categorized by size (Large or Small) and traffic (busy or slow); How could I write a formula (array, I assume) to return the clubs that meet a specific combination (e.g. Large, Busy)?

Attached is a spreadsheet showing what I'm talking about.  The gray area is where I'd like to return the values.  I can make the box as big as the list, then return a "" when the last value is returned.
ArrayFormula-return.xlsx
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You could do a Nested IF possibly:

=IF(C5="Large",IF(D5="Busy","largebusy","Not busy"),"not Large")

If C5=Large then it looks to see if D5 = busy, if not then you get Not busy, if C5 Doesn't equal Large then you get not Large

Will that do what you want?
Most Valuable Expert 2012
Top Expert 2012

Commented:
Is there a reason you just don't do a data filter or pivot table?

Here's an example with data filter - filtered to show Small, Busy clubs.

Cheers,

Dave
ArrayFormula-return.xlsx
Most Valuable Expert 2012
Top Expert 2012
Commented:
Ok, based on a formula, we can use this array-entered formula in the grey cell, then copy down:

[F5]=IFERROR(INDEX(\$B\$2:\$B\$26,SMALL(IF((\$C\$2:\$C\$26=\$F\$3)*(\$D\$2:\$D\$26=\$G\$3),ROW(OFFSET(\$B\$1,,,ROWS(\$B\$2:\$B\$26))),""),ROW()-ROW(\$F\$5)+1)),"")

Ctrl+Shift+Enter to confirm.

The INDEX returns the Club based on the SMALL function which returns the row of that next instance where there's a 2-criteria match.  The IF statement lines up 2-criteria match, returning an array list of matches or "", starting at the beginning of the range in column b, and the last parameter Row()-Rows(\$F\$5)+1 returns the instance number, so based on where you are in the grey cells, it knows which instance to return.

See attached.

Dave
ArrayFormula-return-r1.xlsx

Commented:
Thanks, Dave.  That was perfect.  I know I could use a pivot table or just filter, but I just wanted to see how I could do it this way.  Always trying to learn..and this forum never ceased to amaze me!

Commented:
One last thin, Dave.  Does the "*" server the purpose of forcing an AND condition?
Most Valuable Expert 2012
Top Expert 2012

Commented:
Yes, the * multiplies the True/Falses between the first and second condition, which equates to an AND though the actual result becomes 1/0's.

Dave

Commented:
got it! Thanks, again.

Do more with