asked on

Return Instances where two criteria are met

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

atechnicnate

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?

dlmille

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

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

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!

BBlu

ASKER

One last thin, Dave. Does the "*" server the purpose of forcing an AND condition?

dlmille

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

BBlu

ASKER

got it! Thanks, again.