How to get multiple outputs from XLOOKUP?

[mwl_y]

This is a simplified version of what I need to do; to summarize the situation:
- I have a table with many columns (only 3 are relevant for now)
- Some of the rows may contain identical data in these columns (ex. the duplicate Shirt-Red-A)
- I need a single cell to contain the "Brands" that have items matching the Type and Colour criteria (in this case, the output I need would be "A, B")

1. I know I can use XLOOKUP to match multiple criteria, but it will only provide 1 output; is there a way to get all possible outputs from the XLOOKUP?
2. How would I then get rid of duplicate values? Is there a way to do that within the CONCAT function?

Thanks in advance!

 

[Cubist]

To return multiple matching results, you would use FILTER, then wrap around UNIQUE to remove duplicates. Try:

=UNIQUE(FILTER(Table1[Brand],(Table1[Type]=H3)*(Table1[Colour]=H4)))

If you want to return all the results in a single comma-separated string, then add TEXTJOIN:

=TEXTJOIN(", ",,UNIQUE(FILTER(Table1[Brand],(Table1[Type]=H3)*(Table1[Colour]=H4))))

 

[mwl_y]

To return multiple matching results, you would use FILTER, then wrap around UNIQUE to remove duplicates. Try:

=UNIQUE(FILTER(Table1[Brand],(Table1[Type]=H3)*(Table1[Colour]=H4)))

If you want to return all the results in a single comma-separated string, then add TEXTJOIN:

=TEXTJOIN(", ",,UNIQUE(FILTER(Table1[Brand],(Table1[Type]=H3)*(Table1[Colour]=H4))))

Wow, thank you! Didn't realize the code could be so short haha... this is perfect!