FILTER a Table using Spilled Dynamic Array as part of the "Include" Criteria

[bgeorge11]

1. I have my first sheet (PO Lines) where the user will select a Top Level Part Number from a dropdown list.

2. My second sheet (Part Numbers) contains all the Top Level Part Numbers and Component Part Numbers, formatted in tables, that I will use to make any other dropdowns.

3. Whichever Top Level Part Numbers are input into the PO Lines sheet are then spilled and transposed into cell L3 in the Part Numbers sheet as headers.

4. What I have been trying to do and been unsuccessful is shown below. I have tried multiple different functions but no luck.

The formula will work when setting the include criteria as: ComponentPNs[Top Level PN]=L$3 and dragging the formula but the intention is for this to be dynamic without needing to manually drag any cells. I want the include criteria to be: ComponentPNs[Top Level PN]=L3#.

5. The spill array output I am looking for is shown below.

I plan to make dependent dropdown lists using this output array to use in the PO Lines sheet so when a Top Level Part Number is selected, when the user goes to select the Component Part Number, only the part numbers related to the top level part number will be shown.

Any help with this issue is greatly appreciated. 

[#2]

@bgeorge11 

This is scaled down from your example but gives you the idea:

=FILTER(Table2[Name],ISNUMBER(XMATCH(Table2[Category],L1#)))

[#3]

@bgeorge11 

=VSTACK(TRANSPOSE(SORT(INDIRECT("A2:A"&COUNTA(A:A)))),IFERROR(DROP(REDUCE("",SEQUENCE(1,COUNT(TRANSPOSE(SORT(INDIRECT("A2:A"&COUNTA(A:A)))))),LAMBDA(x,y,HSTACK(x,FILTER(I2:I24,G2:G24=INDEX(TRANSPOSE(SORT(INDIRECT("A2:A"&COUNTA(A:A)))),,y))))),,1),""))

This formula returns the intended result in similar sample data.

[#4]

I tried this previously but I need to have the results spill into separate adjacent columns, rather than a single column, dependent upon the top level part numbers in the L3# heading, as seen in Step 5.

[#5]

I tried this method as well, formatting my data in the same way as in your test sheet, and modifying the formula for my worksheet but only returned the L3# cell values and another row of empty or #N/A cells.

[#6 · Accepted solution]

I think you are looking for 2-D Lambda function here. Maybe something like (note it is untested and just thrown together to give you an idea of how to make it work):

=IFERROR(DROP(REDUCE("",L3#, LAMBDA(p,q, 
   HSTACK(p, VSTACK(q, FILTER(ComponentPNs[Component PNs], 
                              ComponentPNs[Top Level PN]=q,"")
          ))
   )),,1),"")

if you attach a that sample or give a link to it I'd be happy to test it and make sure it works too 🙂

BTW this also spits out the 'header' from L3# but if this formula will go in L4 then something more like this:

=IFERROR(DROP(REDUCE("",L3#, LAMBDA(p,q, 
   HSTACK(p, FILTER(ComponentPNs[Component PNs], 
                    ComponentPNs[Top Level PN]=q,"")
          )
   )),,1),"")

note: I edited it to add the IFERROR wrap to remove the error values make it look nicer 🙂

[#7]

@bgeorge11 

Does it work in the attached file?

[#8]

@bgeorge11 

This is based upon the file provided by @OliverScheurich 

My worksheet formula is presented as a Lambda function

= ComponentListλ(selectedTopLevel)

where ComponentListλ is defined to be

= LET(
    sortSelected, TOROW(SORT(inputList)),
    lists, REDUCE("", sortSelected,
        LAMBDA(acc, selected, HSTACK(acc, FILTER(component, topLevel = selected)))),
    VSTACK(sortSelected, DROP(IF(ISERROR(lists), "", lists), , 1))
  )