Filter Function Dynamic Arrays

[Ashutosh_Dwivedi]

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.

[#2]

@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. 

[#3]

@Riny_van_Eekelen Thanyou for reply

Here file is attached for your references.

[#4]

@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.

[#5]

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

[#6]

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

[#7]

@Ashutosh_Dwivedi 

You want to stack the two arrays?

https://exceluser.com/6534/how-to-stack-and-shelve-dynamic-arrays/

[#8]

@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)"

[#9]

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.

[#10]

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