Excel formula behaves like a Filter function

**ykobure** · 04-19-2015, 08:55 AM

I have with me (attached file) is an Excel FORMULA that returns multiple values with ONLY ONE criteria. What I am aiming for this time is to RETURN MULTIPLE VALUES with MULTIPLE CRITERIA, but it has to behave like an Excel FILTER. Here's the scenario:

Cell B2 has data validation, and every time the selection changes, the table below (A6:F17) will adjust. I already covered this part.
Cell B3 also has data validation, and every time the selection changes, the table below will adjust. The adjustment should be applied on the records displayed by the first selection (B2).
Cell B4 also has data validation, and every time the selection changes, the table below will adjust. The adjustment should be applied on the records displayed by the first (B2) and second selection (B3).
The selection should be in any order, meaning it does not necessarily have to select B2 first, then B3 and B4.
So basically, the function of Filter.

I tried to use the Record Macro and assigned it to a button, but it yields incorrect result.

tessting.xlsm

Hope you guys can help me with this.

Thanks.

**AlKey** · 04-19-2015, 09:41 AM

Try this array formula

In E7 and pull formula to the right and then down

=IFERROR(INDEX(database!A$2:A$24, SMALL(IF(($B$2=database!$A$2:$A$24)*($B$3=database!$B$2:$B$24)*($B$4=database!$C$2:$C$24),ROW(database!$A$2:$A$24)-ROW(database!$A$2)+1), ROW(1:1))),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

another option without Ctrl+Shift+Enter

=IFERROR(INDEX(database!A:A,AGGREGATE(15,6,ROW(database!$A$2:$A$24)/((database!$A$2:$A$24=$B$2)*(database!$B$2:$B$24=$B$3)*(database!$C$2:$C$24=$B$4)),ROWS(A$7:A7))),"")

**ykobure** · 04-22-2015, 11:10 AM

Sorry for getting back here quite late.

Thanks @AlKey for your solution! You came up with a very nice and useful function. However, this still doesn't return my preferred output.

Sorry if my criteria are not well-explained to you, but let me do Take 2:

- When we apply Filter to headers/title of a column, a drop-down button will appear right next to the title
- By the onset of applying Filter, all records are showing
- When we try to select one from the list, the entire record adjust depending on our selection
- Again when we try to select another from the list (using the Filter), the entire record will adjust depending on the current records in the table
- And so on.
- Basically what I prefer is just what the Filter function of Excel does, only in the using VBA and in the layout I indicated in sheet1 entitled "user interface"

In your solution, in sheet1 ("user interface") if B2:B4 are empty, there are no records displaying in the table, and the records will wait to show up if B2:B4 are populated. That's not how Excel Filter works.

Thanks in advance!!