Filter with Formula

Excel can use filters to isolate data in a list.  Formula can also be used to filter a list by specific criteria which you specify.  The intelligent use of Excel formula to generate a filtered list based on a cell value is the premise for the following article.  Filtering with Excel formula in effect will be the outcome.  The technique can be very handy as VBA is not required to achieve the result, just Excel formula.  The following is the Excel formula which will make the Product data based on the drop down (DepName). 

=IFERROR(INDEX(Prod,SMALL(IF(Dep=$D$10,ROW(Dep)-ROW(Data!$A$2)+1),ROWS(B$13:B13))),"")

The above is an array entered formula so you need to press Ctrl Shift Enter. 

The above Excel formula is based on a named range called Prod and a named range called Dep.  These named ranges will need to be set up in order for the formula to work.  The Prod named range is of the Column with the Product and the Dep named range is of the Column with the Department Name.  

In order to extend the Excel formula to generate the Business and Unit two further named ranges are added.  Formula for Business Column.

=IFERROR(INDEX(Bus,SMALL(IF(Dep=$D$10,ROW(Dep)-ROW(Data!$A$2)+1),ROWS(C$13:C13))),"")

Excel formula for Unit Column.

=IFERROR(INDEX(Unit,SMALL(IF(Dep=$D$10,ROW(Dep)-ROW(Data!$A$2)+1),ROWS(D$13:D13))),"")

Both Excel formula are array entered.  So press Ctrl Shift Enter after the formula is in a cell.

The Excel file attached shows the above example.