I have a data set and used google sheets and the filter function to return a set of outputs based on a criteria That filter function doesn't exist on my version of excel of PC What is an alternative I can use to get the same output
Data Set:
Formula used in sheets:
=FILTER(A2:A16,K2:K16="Operations")
Output would be a list of employee names who were in the department operations
Any help on replicating this on excel would be much appreciated.
CodePudding user response：
As 
• Formula used in cell F2
=IF(ROWS($F$2:F2)<=COUNTIF($D$2:$D$16,"Operations"),
INDEX($A$2:$A$16,AGGREGATE(15,6,(ROW($A$2:$A$16)-ROW($A$2) 1)
/($D$2:$D$16="Operations"),ROW(A1))),"")
The reason I have used a COUNTIF() Function is that, it gives the counts of number employees in Operations Department and gives the output for those accordingly, thus excluding the use of IFERROR() Function & makes more robust formula!
However, these are the few alternatives, which you may try as well,
=IFERROR(
INDEX($A$2:$A$16,AGGREGATE(15,6,(ROW($A$2:$A$16)-ROW($A$2) 1)
/($D$2:$D$16="Operations"),ROW(A1))),"")
Or,
=IFERROR(
INDEX($A$2:$A$16,SMALL(IF($D$2:$D$16="Operations",
ROW($A$2:$A$16)-ROW($A$2) 1),ROW(A1))),"")
Note: Using SMALL() Function you may need to press CTRL SHIFT ENTER based on your Excel Version, however using AGGREGATE() usually it doesn't require! Also, since i have used the Department in Column D you may need to adjust the formula in your workbook with Column K. Therefore the formula will be
=IF(ROWS($A$2:A2)<=COUNTIF($K$2:$K$16,"Operations"),
INDEX($A$2:$A$16,AGGREGATE(15,6,(ROW($A$2:$A$16)-ROW($A$2) 1)
/($K$2:$K$16="Operations"),ROW(A1))),"")