Forum Discussion

amber_l's avatar
amber_l
Copper Contributor
Sep 10, 2021
Solved

return multiple lookup values where two seperate matches occur

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

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

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.

excel
Formulas and Functions

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 10, 2021

    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!

  • ky13ao's avatar
    ky13ao
    Copper Contributor
    Sep 10, 2021
    F1=textjoin(",",1,if((A:A=D1)*(B:B=E1),C:C,""))

Resources