No idea how to title this one.
Suppose I have the above table. I want the column "used in" to display the name of every food that uses the food on this row, to look like this:
The separator is unimportant, I just used a ; as I like how it looks. How would I go about this?
>Solution :
Try the following formula:
• Formula used in cell B3
=TEXTJOIN(";",,FILTER(A$3:A$6,1-ISERR(SEARCH(A3,C$3:C$6)),"-"))
Or Using one single dynamic array formula called BYROW() passing a custom LAMBDA() calculations. Do note one vital point, that TEXTJOIN() function has character limitations hence using the fill down approach is more appropriate in this scenario, however, if its within the limit you could try this as well:
=LET(
_food, A3:A6,
BYROW(A3:A6,LAMBDA(x,TEXTJOIN(";",,
FILTER(_food,ISNUMBER(SEARCH(x,C3:C6)),"-")))))