Create a dynamic list from multiple criteria in da

Question

How do I create a list from multiple criteria ?

Item    Type    Data1   Data2   Data3   Data4
Apple   Fruit   81      15      14      11 
Apple   Fruit   77      73      59      42 
Cashew  Nut     16      6       1       1 
Cashew  Nut     100     51      21      12 
Peach   Fruit   75      54      5       2 
Apple   Fruit   64      12      11      1 
Apple   Fruit   47      31      24      15 
Peach   Fruit   6       6       1       0 
Peach   Fruit   68      58      56      47 

Criteria = Fruit & (Data2 or Data3 or Data4)>20

Result (Formatted List of Items with No Gaps)

Apple
Peach
Apple
Peach

Answer 1

This can actually be worked out using the INDEX function in its array form.

        

The formula in H2 is,

=IFERROR(INDEX(A$2:A$99, SMALL(INDEX(ROW($1:$98)+(((B$2:B$99="fruit")+(((D$2:D$99>20)+(E$2:E$99>20)+(F$2:F$99>20))>0))<>2)*1E+99, , ), ROW(1:1))), "")

Fill down as necessary to catch all of the results and maybe a few rows more for future consideration. You will probably wish to put Fruit and 20 in separate cells and reference the cells within the formula rather than edit the formula itself for different criteria.

When editing this formula for your own purposes, remember that ROW(1:98) is the position within A2:A99 and not the actual row on the worksheet. ROW(1:1) is simply a counter for the SMALL function's k parameter and will increase (e.g. 1, 2, 3, 4, etc) as you fill the formula down.

This is a standard formula. Ctrl+Shift+Enter↵ is neither necessary or desired.