 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.

3 Replies
best response confirmed by amber_l (New Contributor)
Solution

# Re: return multiple lookup values where two seperate matches occur

F1=textjoin(",",1,if((A:A=D1)*(B:B=E1),C:C,""))

# Re: return multiple lookup values where two seperate matches occur

awesome that worked! thank you

# Re: return multiple lookup values where two seperate matches occur

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!