Return multiple match index lookups into one cell with multiple criteria.

[the133448]

Hi,

So I have a case where I need to return all results in a column where 2 criteria are met into one cell.

My table looks something like this

+-----------+------+-------+
|    Job    | Type | Name  |
+-----------+------+-------+
| Tree      | AA   | Bob   |
| Apple     | FF   | John  |
| Banana    | FF   | John  |
| Grape     | CC   | James |
| Mango     | FF   | Paul  |
| Chocolate | AA   | Angus |
+-----------+------+-------+

I currently use a forumla like this:

 =IFERROR(INDEX(Table1[Job],MATCH(1,(Table1[Name]=B12) * (Table1[Type]="FF"),0)),"")

Where B12 is an lookup name. However if I was to write John, I'd only get Apple. What I want is "Apple, Banana".

How would I go around doing that?

 

[the133448]

Solved by using the TEXTJOIN formula introduced in Excel 365.

=TEXTJOIN(", ",1,REPT(Table1[Job],1*((Table1[Name]=B12)* (Table1[Type]="FF"))))