HDI - Return multiple keywords in a text column based on a set of available keywords

I've checked around the forum and haven't been able to find a solution that matches what I need (at least using the examples given thus far). I have a text field where users have historically been able to type whatever they want. In converting to a new system, we have a list of available keywords which will be used in a drop down. For the conversion, we want to return each keyword contained in the text field, ideally with comma separation.

I am attaching the following spreadsheet. The Injuries sheet contains examples of text entered. I have included a highlighted column using the formula below. I have then created a manual 3rd column which has my desired results. So far, with the formula below, I can only get it to return one keyword value based on the Keywords tab. It appears it is always pulling the last available one in that list if there are multiple.


=IFERROR(LOOKUP(1E+100,SEARCH(Keywords!$A$2:$A$92,A290),Keywords!$A$2:$A$92),"")

Hi,

Concatenating multiple entries into a single cell without resource to VBA is not so feasible, particularly if the number of returns is large.

If you are willing to accept separate cells for each return, then this array formula (important that you know how to enter this type of formula in Excel) in D2 and copied across and down will give you your desired results:

=IFERROR(INDEX(Keywords!$A$2:$A$10,SMALL(IF(ISNUMBER(SEARCH(Keywords!$A$2:$A$10,Injuries!$A2)),ROW(Keywords!$A$2:$A$10)-MIN(ROW(Keywords!$A$2:$A$10))+1),COLUMNS($A:A))),"")

If not, then I trust someone will shortly offer you the required VBA solution.

Regards