+ Reply to Thread
Results 1 to 5 of 5

How to creat a dynamic array (Filter) based on a dynamic array (Filter) of another table

  1. #1
    Registered User
    Join Date
    03-15-2023
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    2

    How to creat a dynamic array (Filter) based on a dynamic array (Filter) of another table

    I would like to creat a dynamic array (FILTER) based on the outcome of another FILTER, however, I get #N/A as result

    I have two tables: Table1 (A2:C11 - columns Code1, Code2 and ItemCode) and table2 (A14:B30 - Columns ItemCode and description)
    I have the FILTER function in cell E3 on table1, resulting in 4 values picked from column 3 (ItemCode) of table1
    I have FILTER_2 in cell E15.
    If I use FILTER(Table2;Table2[ItemCode]=E3;"No Match"), the resulting Array only gives me the outcome with the actual value of Cell E3
    I would like to use the whole dynamic array in cell E3.
    BUT
    If I use FILTER(Table2;Table2[ItemCode]=E3#;"No Match"), the result is #N/A

    Anyone an idea?
    Please see enclosed file

    Dynamic Array on dynamic array test.xlsx

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: How to creat a dynamic array (Filter) based on a dynamic array (Filter) of another tab

    Is this it?

    =DROP(REDUCE(0,E3#,LAMBDA(x,y,IFERROR(VSTACK(x,FILTER(Tabel2,Tabel2[ItemCode]=y,y)),"No Match"))),1)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: How to creat a dynamic array (Filter) based on a dynamic array (Filter) of another tab

    Please note, I edited a mistake out of the above formula/attachment a minute after I posted it....

  4. #4
    Registered User
    Join Date
    03-15-2023
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    2

    Re: How to creat a dynamic array (Filter) based on a dynamic array (Filter) of another tab

    Dear Glenn, THANK YOU!
    Perfect solution. I was not familiar with the LAMBDA function, so it took me a while to fully understand your solution.
    Really nice!

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: How to creat a dynamic array (Filter) based on a dynamic array (Filter) of another tab

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 06-01-2022, 01:59 AM
  2. [SOLVED] COUNT Dynamic Array based on Date to return Dynamic Array Spill Range
    By dluhut in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-06-2022, 01:14 PM
  3. Replies: 7
    Last Post: 12-10-2021, 02:17 PM
  4. How to make filter value array dynamic
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2019, 12:18 PM
  5. FILTER dynamic array with skipped columns?
    By istank in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-11-2018, 02:04 PM
  6. [SOLVED] Dynamic array filter formula
    By johnmitch38 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-14-2018, 05:40 AM
  7. Sort and Filter 2D Dynamic Array
    By willia97 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-23-2014, 07:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1