Populate Keyword List based on Pattern

I copy/pasted the data all onto one sheet to make it simplier for an example.

I also used some index numbers, split the prefix/suffix of the PATTERN into two columns, and used helper columns, all to make the calculation simplier to follow; it's not impossible to nest all of it, it's just that it would be much harder to follow.

(For example, if you left the PATTERN string as it was, you would have to run an IF to determine where the XXX was on either side of the DRINK to determine whether it was a prefix or a suffix, and then replace "XXX" with "" before concatenation. Not impossible, just too much complication for an example).

Anyway, solve this by using INDIRECT to find the row, based on the division and modulus of the index compared to the counts of the word.

You can use this formula on row 1 of the results sheet:

=IF(ROWS($1:1)>(COUNTA(drinks!A:A)-1)*(COUNTA(pattern!A:A)-1),"",SUBSTITUTE(INDEX(pattern!A:A,MOD(ROWS($1:1)-1,COUNTA(pattern!A:A)-1)+2),"XX",INDEX(drinks!A:A,INT((ROWS($1:1)-1)/(COUNTA(pattern!A:A)-1))+2)))

and then just copy down as far as you need to (until you get blanks).

I've added some extra drinks and another pattern in the attached file to show that the formula automatically adjusts.

Hope this helps.

Pete

Thanks a lot to both of you!!
It's exactly what I needed