Forum Discussion

amber_l's avatar
amber_l
Copper Contributor
Sep 09, 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 availa...
excel
Formulas and Functions
  • ky13ao's avatar
    ky13ao
    Sep 09, 2021
    F1=textjoin(",",1,if((A:A=D1)*(B:B=E1),C:C,""))
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!

Resources