Forum Discussion

Ashutosh_Dwivedi's avatar
Ashutosh_Dwivedi
Copper Contributor
Oct 05, 2021

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.

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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. 

Resources