Need help with formula

[BluRid]

Hey, i got a list of products and the components of every product and need a formula to lookup whatever component number i need and find all the products that uses that component. The list looks like this:

So what i need is: Lets take A40142011 at line 18 as an example: I want the formula to search for A40142011 in the list and find all products (all components of the product has the same "Stykkliste" number, so everything with the number 17378 is components of the product A40142002. So i want the output of the formula to be "Component is used when creating product: A40142002" and if multiple products is made by using that component it will list all of those aswell based on matching "Stykkliste" numbers. Basically it should look for matches, check stykkliste number and then return the product material nr that belongs to the stykkliste number or numbers.

Materialnummer: end product
Komponenter: components
Stykkliste: number that connects the end product to the components

 

[RobP]

Hi, I'm not sure if I quite understood your request, but this was my interpretation :

In Cell L3, I typed the Komponent number you were referencing. Then I put this formula below in cell Q4 for example :

=LET(styk,FILTER(B5:B36,L5:L36=L3,""),result,SCAN(0,styk,LAMBDA(a,v,XLOOKUP(v,B5:B36,D5:D36,""))),result)

That generates a list of the materialnummers that the Komponent is related to.

Let me know if thats what you meant ?

thanks
Rob

 

[Fluff]

My take on this

=LET(f,FILTER(B5:D100,J5:J100=J3),FILTER(f,INDEX(f,,2)<>""))

 

[RobP]

oops sorry, I realised I had a couple spare columns inserted, so formula should be this: with komponent in J3

=LET(styk,FILTER(B5:B36,J5:J36=J3,""),result,SCAN(0,styk,LAMBDA(a,v,XLOOKUP(v,B5:B36,D5:D36,""))),result)

I knew @Fluff would embarass me with something more simple as soon as I posted something

good job ..
Rob