Return Instances where two criteria are met

BBlu
BBlu used Ask the Experts™
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

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!

Author

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

Author

Commented:
got it! Thanks, again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial