Excel Advance: Filtering data automatically based on condition using functions.

Sample file download: Filter_Data_Based_On_Condition      Zip File (10 KB)

How to filter data using function in Excel. In above screenshot there are some data in left table. In right table filtered data are shown.

There are some tasks list and different task is in different status. We want to filter tasks those are already complete. Whenever a task is mark as complete it will automatically appear in filtered list. To achieve these filtering use following formula.

=IFERROR(INDEX($B$4:$E$18,SMALL(IF($E$4:$E$18="Complete",ROW($E$4:$E$18)-ROW($B$3),""),ROWS($B$4:$B4)),COLUMNS($B$4:B$4)),"")

*** Note: This is an array formula. So, must press Ctrl+Shift+Enter after typing the formula.

As above screenshot put the formula in G4 cell and fill the formula right and down.