SOLVED

return multiple lookup values where two seperate matches occur

%3CLINGO-SUB%20id%3D%22lingo-sub-2738622%22%20slang%3D%22en-US%22%3Ereturn%20multiple%20lookup%20values%20where%20two%20seperate%20matches%20occur%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738622%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20all%2C%20this%20one%20may%20be%20a%20bit%20tricky%20and%20I%20cant%20think%20of%20the%20best%20way%20to%20get%20what%20I%20need.%3C%2FP%3E%3CP%3EHere%20is%20my%20data%20below%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22amber_l_0-1631242409520.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309496i120FEB19ECBC54B4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22amber_l_0-1631242409520.png%22%20alt%3D%22amber_l_0-1631242409520.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EColumn%20A%20%3D%20product%20code%3C%2FP%3E%3CP%3EColumn%20B%20%3D%20length%20available%3C%2FP%3E%3CP%3EColumn%20C%20%3D%20colour%20available%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELets%20take%20product%20code%2000134%20for%20this%20example.%3C%2FP%3E%3CP%3EI%20need%20this%20as%20the%20final%20result%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22amber_l_1-1631242780739.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309497i0BC42B3A55719437%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22amber_l_1-1631242780739.png%22%20alt%3D%22amber_l_1-1631242780739.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20essentially%20need%20to%20see%20the%20product%20code%20and%20the%20size%20its%20available%20and%20all%20the%20colours%20available%20in%20that%20size.%20At%20the%20moment%20it%20is%20all%20on%20individual%20lines.%20Is%20this%20something%20that%20is%20possible%3F%20I%20need%20to%20be%20able%20to%20do%20this%20completely%20in%20excel%20and%20cant%20have%20any%20external%20programming%20to%20complete%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2738622%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2738636%22%20slang%3D%22en-US%22%3ERe%3A%20return%20multiple%20lookup%20values%20where%20two%20seperate%20matches%20occur%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738636%22%20slang%3D%22en-US%22%3EF1%3Dtextjoin(%22%2C%22%2C1%2Cif((A%3AA%3DD1)*(B%3AB%3DE1)%2CC%3AC%2C%22%22))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2740482%22%20slang%3D%22en-US%22%3ERe%3A%20return%20multiple%20lookup%20values%20where%20two%20seperate%20matches%20occur%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2740482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F467643%22%20target%3D%22_blank%22%3E%40amber_l%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20365%20can%20support%20very%20different%20solutions.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20UNIQUE(Code%5CSize)%0A%0A%3D%20TEXTJOIN(%22%2C%20%22%2C%2C%0A%20%20%20%20%20%20FILTER(%0A%20%20%20%20%20%20%20%20%20Colour%2C%0A%20%20%20%20%20%20%20%20%20(Code%3D%40distinctCode)*(Size%3D%40distinctSize)%0A%20%20%20%20%20%20)%0A%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20the%20first%20formula%20returns%20every%20unique%20combination%20of%20Code%20and%20Size%20as%20a%20single%20array%2C%20whilst%20the%20second%20returns%20the%20colour%20lists%20one%20at%20a%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20little%20interest%20now%2C%20but%20something%20that%20could%20be%20relevant%20when%20Lambda%20functions%20are%20released%20for%20general%20use%20is%2C%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20COMBINE%2C%20%20LAMBDA(c%2Cs%2CTEXTJOIN(%22%2C%20%22%2C%20%2C%20FILTER(Colour%2C%20(Code%3Dc)*(Size%3Ds))))%2C%0A%20%20%20%20arrays%2C%20%20%20CHOOSE(%7B1%2C2%7D%2CCode%2CSize)%2C%0A%20%20%20%20distinct%2C%20UNIQUE(arrays)%2C%0A%20%20%20%20distinctCode%2C%20INDEX(distinct%2C%2C1)%2C%0A%20%20%20%20distinctSize%2C%20INDEX(distinct%2C%2C2)%2C%0A%20%20%20%20colours%2C%20MAP(distinctCode%2CdistinctSize%2C%20COMBINE)%2C%0A%20%20%20%20CHOOSE(%7B1%2C2%2C3%7D%2C%20distinctCode%2C%20distinctSize%2C%20colours)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhich%20will%20return%20the%20entire%20table%20of%20results%20as%20a%20single%20formula.%26nbsp%3B%20This%20is%20serious%20programming%20within%20a%20cell!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2738650%22%20slang%3D%22en-US%22%3ERe%3A%20return%20multiple%20lookup%20values%20where%20two%20seperate%20matches%20occur%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738650%22%20slang%3D%22en-US%22%3Eawesome%20that%20worked!%20thank%20you%3C%2FLINGO-BODY%3E
New Contributor

Hey all, this one may be a bit tricky and I cant think of the best way to get what I need.

Here is my data below

amber_l_0-1631242409520.png

Column A = product code

Column B = length available

Column C = colour available

 

Lets take product code 00134 for this example.

I need this as the final result

amber_l_1-1631242780739.png

I essentially need to see the product code and the size its available and all the colours available in that size. At the moment it is all on individual lines. Is this something that is possible? I need to be able to do this completely in excel and cant have any external programming to complete this.

3 Replies
best response confirmed by amber_l (New Contributor)
Solution
F1=textjoin(",",1,if((A:A=D1)*(B:B=E1),C:C,""))
awesome that worked! thank you

@amber_l 

Excel 365 can support very different solutions.  

= UNIQUE(Code\Size)

= TEXTJOIN(", ",,
      FILTER(
         Colour,
         (Code=@distinctCode)*(Size=@distinctSize)
      )
   )

where the first formula returns every unique combination of Code and Size as a single array, whilst the second returns the colour lists one at a time.

 

Of little interest now, but something that could be relevant when Lambda functions are released for general use is, 

= LET(
    COMBINE,  LAMBDA(c,s,TEXTJOIN(", ", , FILTER(Colour, (Code=c)*(Size=s)))),
    arrays,   CHOOSE({1,2},Code,Size),
    distinct, UNIQUE(arrays),
    distinctCode, INDEX(distinct,,1),
    distinctSize, INDEX(distinct,,2),
    colours, MAP(distinctCode,distinctSize, COMBINE),
    CHOOSE({1,2,3}, distinctCode, distinctSize, colours) )

which will return the entire table of results as a single formula.  This is serious programming within a cell!