On the sheet Dynamic 3-5 Checklist I want to use the cells in blue (columns B, D, F, etc.) as a reference to look for the text in the range B9:P21 on sheet Dynamic Grade 5, then display the words in column A.

For example, C1 on Dynamic 3-5 Checklist should be populated with the text Typing Program from B12 on Dynamic Grade 5. Also, the text from C1 appeared for any other project the name of that project from the A column would also appear in C1.

• If you want to get only the unique values, prepend the arrayformula with unique.

=textjoin(CHAR(10), true, unique(arrayformula(if(isblank(B2:B), "", ifna(vlookup("*"&B2:B&"*", {'Dynamic Grade 5'!B$9:B,'Dynamic Grade 5'!$A$9:$A}, 2, false), "")))))

Note:

• When copying the formula to other cells in 1:1 range, you only need to update the 1st column of {} array since B2:B correctly adjusts automatically when copied. See the pattern on the inserted formula on your sheet and you'll get the idea

UPDATE:

C2:

=ifna(join(CHAR(10), index(filter({'Dynamic Grade 5'!B$9:B, 'Dynamic Grade 5'!A$9:A}, 'Dynamic Grade 5'!B$9:B = B2), , 2)), "")