Excel (365) Create array that shows matching values within long text cell

I’m using Excel for MS 365 and I could not find how to assemble either a text or an array of pieces of a long text that has match values of another column.
I Tried to use TEXTJOIN, but all my attempts retrieved errors, and the closest formula I could find was: index($A$2:$A$31,match(1,index(isnumber(search(" "&A$2:$A$31&" "," "&c2&" "))+0,0),0)) , however it retrieves me only first value matched.

Scenario is:
First column I listed most common ingredients used in cooking
Second column I listed the appliances available
Third Column I am copying the full text of recipes (lines would increased thru time)
Forth and fifth columns are where I’m interested on create the formula.

I’ll focus in just one cell, to make easier to understand the scenario and try to have somebody offering me a formula. Kindly ask help.

Example of a small hypothetical case, Cell D2 would show “Flour Butter Milk” because at cell C2 contains a long text of cake recipe listing ingredients and instructions.

If I use index($A$2:$A$8,match(1,index(isnumber(search(" "&A$2:$A$8&" "," "&c2&" "))+0,0),0)) it retrieves me only Flour.

I will replicate same formula concept to create the comparison of C2 with the range of $B$2:$B$5 to create value “Mixer 10-in round pan”
Table would look like

A B C D E
1 Ingredients Appliances Recipe IngUsed AppUsed
2 Flour Planetary Mixer *A (see below) formula formula
3 Sugar Blender
4 Butter 10-in round pan
5 Salt Air Fryer
6 Chocolate
7 Milk
8 Corn Oil

*A would be a long text (also with CRLF) like

1 cup white sugar ½ cup unsalted butter 2 large eggs 2 teaspoons
vanilla extract 1 ½ cups all-purpose flour 1 ¾ teaspoons baking powder
½ cup milk Directions Gather all ingredients. Overhead of white cake
ingredients in various bowls and measuring cups. Preheat the oven to
350 degrees F (175 degrees C). Grease and flour a 9-inch square cake
pan. Cream sugar and butter together in a mixing bowl. Add eggs, one
at a time, beating briefly after each addition. Mix in vanilla. High
angle of cake batter being mixed in a standing mixer with milk and
flour in bowls off to the side. Combine flour and baking powder in a
separate bowl. Add to the wet ingredients and mix well. Add milk and
stir until smooth. Flour being added to a cake batter in a standing
mixer. Overhead of a creamy cake batter in a bowl. Pour batter into
the prepared 10-in round pan. Overhead of a cake batter in a cake pan.
Bake in the preheated oven until the top springs back when lightly
touched, 30 to 40 minutes. Overhead of a baked white cake in a cake
pan. Remove from the oven and cool completely. Enjoy!

Number of lines on column A, B and C are way too higher, I’m just bringing a subset.

Thanks in advance

In Office 365 you could use the following:

=TOCOL(REPT(A2:A8,SEARCH(A2:A8,C2)^0),2) and
=LET(b,TOCOL(B2:B8,1),TOCOL(REPT(b,SEARCH(b,$C2)^0),2))

enter image description here

Leave a Comment