Is it possible to insert the index function in the filter function?

I'm guessing you are looking for something like this:

Cell D1 contains the column number (you would reference the cell with your Pop-Up Menu).

This example simply returns an array of values in the chosen column that are greater than 5.

The 0 row-index means array of values for the entire column.

When you change the column number you get a different filtered array.

SG

Dubbia wrote:

What I would like to achieve is this result that I obtained with this non-dynamic formula

FILTER(DATABASE::A;COLOURS≠0;”")

I tried this formula, using the pop-up menu,

FILTER(DATABASE::A;MATCH(MENU'::A1;DATABASE::$1:$1;0)≠0;””)

Try this instead (just an extension of my "guess" above):

=FILTER(INDEX(DATABASE::A,0,1),INDEX(DATABASE::A:D,0,MATCH(MENU::A1,DATABASE::$1:$1))≠"")

The array is column A. The 0 row-index tells INDEX to take the whole column.

The test-array is a column chosen dynamically from the table. The MATCH returns the chosen column number by matching the value chosen in your Pop-Up Menu in A1 of the 'Menu' table against the column headers in 'DATABASE' and INDEX uses that column number to return an array of the values in that whole column (again the row-index is 0).

SG

I'm not entirely sure I understand the ask now...

I *think* what you're saying is that you want to choose from the 'Menu' either 'Colours', 'Age', or 'Name' and have that act as the selector, is that right?

Do you mean that if the 'Menu' is set to 'Colours' it should return all Codes that have any value in the Colours column? That's even easier:

Assuming that MENU::A1 is a popup that contains the same values as the headers in DATABASE, you can set List::A2 to:

=FILTER(Database::A,
	XLOOKUP(MENU::A1,Database::$1:$1,DATABASE::B:D,"",0)≠"",
	"")

This first performs an XLOOKUP() to find the matching column based on Menu::A1, looking in row $1 of the DATABASE table.

This result (the column that matches the MENU selection) is then passed into the FILTER() to find non-empty cells.

It is possible to have the FILTER() check multiple conditions, but there are some caveats.

You're probably already aware that in your example, List::A2 can check for any one of the conditions via:

=FILTER(DATABASE::A,DATABASE::B=MENU::A1,"")

to filter on COLOURS, or:

=FILTER(DATABASE::A,DATABASE::C=MENU::A2,"")

to filter on AGE, etc.

To effect an AND condition to compare multiple values, you just have to reallise that the test condition returns an array of boolean values (TRUE/FALSE, or 1/0). You can multiply these together to perform a logical AND:

=FILTER(Database::A,
        (Colors=Menu::A1)×(Age=Menu::A2)×(Name=Menu::A3),
         "")

This will perform a filter returning values from Database::A that match all three Colours/Age/Name pop ups.

Note that you may have to consider empty cells. This model will always match on three, so it doesn't support, say, ANY color for Rachel, it will only match if all three categories match. that may or may not be a problem for your use case.

Dubbia wrote:

The formula works, but it takes the whole column without filtering

Did you forget to post "the formula"?

What does your table look like?

Specifics help in answering the question.

Very likely there is a formula that will do what you want.

SG