Can we achieve the following using an Excel formula? The output of the formula will be the Result co-CodePudding

Month	Brand	Product	Result
January	Apple	Iphone	Apple:Iphone,Ipad,Imac;Lenovo:Yoga,Thinkpad
January	Apple	Ipad	Apple:Iphone,Ipad,Imac;Lenovo:Yoga,Thinkpad
January	Apple	Imac	Apple:Iphone,Ipad,Imac;Lenovo:Yoga,Thinkpad
January	Lenovo	Yoga	Apple:Iphone,Ipad,Imac;Lenovo:Yoga,Thinkpad
January	Lenovo	Thinkpad	Apple:Iphone,Ipad,Imac;Lenovo:Yoga,Thinkpad
February	Apple	Imac	Apple:Imac;Lenovo:Thinkpad
February	Lenovo	Thinkpad	Apple:Imac;Lenovo:Thinkpad

CodePudding user response：

In D2 put:

=BYROW($A$2:$A$8,
    LAMBDA(a,
        LET(rng,a,
            mb,$A$2:$B$8,
            m,$A$2:$A$8,
            b,$B$2:$B$8,
            p,$C$2:$C$8,
            TEXTJOIN(";",,UNIQUE(
                 BYROW(FILTER(mb,m=rng),
                 LAMBDA(a,INDEX(a,,2)&":"&
                    TEXTJOIN(",",,FILTER(p,(m=INDEX(a,,1))*(b=INDEX(a,,2)))))))))))