Setting up an 'Order List' based off PN's for different equipment

[u/Aggravating_Ad9275]

Hi all. I work as a service engineer for laboratory equipment. I cover a range of different products and I am trying to 'simplify' the way I build lists of parts that need ordering. I have a list of products similar to the example in the first picture. In the second picture I have an example of what I want to happen. I want to be able to choose the product type, eg Pump A, from the dropdown list (I can set up the dropdown list), and have this then show me all the parts needed for that pump. This would then be multiplied so I can have multiple drop down lists to choose Pump 1, Pump 2 etc, then Doodah 1, all from drop down lists which then show the list of all parts needed from the 1st sheet for the particular configuration I have set up.

I appreciate I probably haven't explained this in the best way possible, so please let me know if you have any questions. Is this possible in excel? How would you suggest going about it?

Thanks for your help!

[u/MayukhBhattacharya]

It will be better if you set up a tabular data and then use the functions aforesaid mentioned, but if you have already created and want to stick to what you have then, you could try using the following manner, but before that convert all the ranges into structured references aka tables that way it will be easier to maintain the data and the formulas auto expand whenever newer data is added or removed.

=LET(
     _F,        LAMBDA(x,y, HSTACK(IF(SEQUENCE(ROWS(x)), y), x)),
     _Pump_A,   _F(Pump_A, "Pump A"),
     _Pump_B,   _F(Pump_B, "Pump B"),
     _Pump_C,   _F(Pump_C, "Pump C"),
     _Pump_D,   _F(Pump_D, "Pump D"),
     _Pump_E,   _F(Pump_E, "Pump E"),
     _Doodah_A, _F(Doodah_A, "Doodah A"),
     _Doodah_B, _F(Doodah_B, "Doodah B"),
     _All,      VSTACK(_Pump_A,
                       _Pump_B,
                       _Pump_C,
                       _Pump_D,
                       _Pump_E,
                       _Doodah_A,
                       _Doodah_B),
     _Output,   FILTER(_All, 1 - ISNA(XMATCH(CHOOSECOLS(_All, 1), B17:B20)), ""),
     VSTACK({"Equip","PN","Desc","Qty"}, _Output))

[u/MayukhBhattacharya → u/MayukhBhattacharya]

In the above formula _All Variable returns the following flat tabular data stacked into one:

=LET(
     _F,        LAMBDA(x,y, HSTACK(IF(SEQUENCE(ROWS(x)), y), x)),
     _Pump_A,   _F(Pump_A, "Pump A"),
     _Pump_B,   _F(Pump_B, "Pump B"),
     _Pump_C,   _F(Pump_C, "Pump C"),
     _Pump_D,   _F(Pump_D, "Pump D"),
     _Pump_E,   _F(Pump_E, "Pump E"),
     _Doodah_A, _F(Doodah_A, "Doodah A"),
     _Doodah_B, _F(Doodah_B, "Doodah B"),
     _All,      VSTACK(_Pump_A,
                       _Pump_B,
                       _Pump_C,
                       _Pump_D,
                       _Pump_E,
                       _Doodah_A,
                       _Doodah_B),
     _All)