# Filter Function Dynamic Arrays

Copper Contributor

# Filter Function Dynamic Arrays

Dear All

I have one question for all of you, is there any possible condition in filter function in multiple ranges or with nested formula use of filter function....................

Q.: I want to use multiple range in different sheets in filter function i.e. =filter((sheet1!a1:a40, sheet2!a1:a40), (sheet!a1:a40)*(sheet2!a1:a40),""). But final result is #N/A, How can i solve it.

Q2.: Formula should be spill.

9 Replies

# Re: Filter Function Dynamic Arrays

@Ashutosh_Dwivedi The formula you provided doesn't make much sense (to me at least). Perhaps you are trying to spill matching cells from both ranges like this:

``=FILTER((Sheet1!A1:A40),(Sheet1!A1:A40)=(Sheet2!A1:A40),"")``

If not, perhaps better to upload a file with data in it, explaining what your intend to achieve with the formula.

# Re: Filter Function Dynamic Arrays

Here file is attached for your references.

# Re: Filter Function Dynamic Arrays

@Ashutosh_Dwivedi Sorry. Don't understand your intentions. Do you perhaps want to append the two lists in DATA1 and DATA2? FILTER is used to extract data from a range (preferably a structured table) based on one criterion or multiple criteria.

# Re: Filter Function Dynamic Arrays

@Riny_van_Eekelen Dear sir
I want to first spill data1 after finish the data1 it will continue to data2 sheet. It's possible.

# Re: Filter Function Dynamic Arrays

@Ashutosh_Dwivedi Unless I'm mistaken, that's not possible with a formula. You could use PowerQuery. See attached.

# Re: Filter Function Dynamic Arrays

@Detlef Lewin
Dear Sir
Exactly i want the same for two array apend with the arrays stacked one on top and second on bottom after finish the first data.
And please send if you have file uses this formula.: "=LET(name1, name_value1, [name2, name_value2], […, …], named_result)"

# Re: Filter Function Dynamic Arrays

Dear Mr. @Riny_van_Eekelen
Thanks for giving the time to solve our query, but this is not my solution which I'm expecting all of you, In result sheet you must remove the "NO" in power query calculation.

Thankyou so much.

# Re: Filter Function Dynamic Arrays

@Ashutosh_Dwivedi,
you can use formula like this one
=filter(vstack(sheet1!a1:a40, sheet2!a1:a40))