Can dynamic array functions be used as a source in a list validation ("dropdown") in Excel?

user_12418842 0
Viewed 358

I would like to use the new dynamic array functions in Excel (like FILTER, UNIQUE, SORT) as a source for list validations.

In the following example, all values in column A where the corresponding value in column B is at least 500 should appear. I use the following formula:

=FILTER($A$2:$A$7;$B$2:$B$7>=500)

Using Excel FILTER function as a source for dropdown

Unfortunately, when I use the formula from cell D2 as a source, I get an error message:

The Source currently evaluates to an error.

I have also tried to put the formula into a name and then reference the name as a source. But that gives me the same error. The only workaround was to reference cell D2 with a hash sign:

=D2#

Reference spill range with hash tag in Excel

However, I would prefer if I could use those dynamic array functions directly (either via name manager or in the validation interface). Is there any way to achieve that?

dynamic-arrays excel-formula excel
1 Answers

Try this, it works to me. Note that the reference M1 is the first value of the validation column. (I used M in my case)

=INDEX($B:$B;MATCH(M1;$A:$A;0))>=500
user_150603900
Related