Try, in B2,
array-entering this formula:
Code:
=TEXTJOIN(", ",TRUE,IF(MATCH(IF(ISNUMBER(SEARCH(Varieties!$A$2:$A$20,$A2)),Varieties!$B$2:$B$20,""),IF(ISNUMBER(SEARCH(Varieties!$A$2:$A$20,$A2)),Varieties!$B$2:$B$20,""),0)=ROW(Varieties!$A$1:$A$19),IF(ISNUMBER(SEARCH(Varieties!$A$2:$A$20,$A2)),Varieties!$B$2:$B$20,""),""))
and copy down.
Not forgetting to add
Rose to the list of Varieties on the
Varieties sheet.
The simpler array-entered:
Code:
=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(Varieties!$A$2:$A$20,$A2)),Varieties!$B$2:$B$20,""))
works but because you sometimes have multiple finds (eg. in row 7 you have
HA HA MARL PINOT NOIR where both
Pinot and
Noir are found so the result shows as
Pinot Noir,Pinot Noir, the more complex formula eliminateas those.
I'll leave you to envelop that formula in an
IF statement to show
Other when none are found.