Modify TEXTJOIN formula to avoid duplicate returns

Hi.

I'm using this formula to return keywords based on a search of one cell

Please Login or Register  to view this content.

How can I avoid duplicated keywords if the search finds two target words in F2 with the same Keywords?

eg
Keywords! A1= Red B1=Colour
Keywords! A2= Blue B2=Colour

If F2 contains 'Red' and 'Blue' I only want 'Colour' returned once (as opposed to 'Colour>Colour' which is what I currently get)

Thanks.

One option:

Formula:

Please Login or Register  to view this content.

could likely be shortened ...

Hi XLent.

Thanks for your reply. When I try that I just get '0' as the output. I've reduced my spreadsheet down to minimum columns so that you can see it in action and attached.

Thanks, Phil.

my bad, prefix the ranges in the match with Keywords, given this is where the terms are located; below based on your file:

Formula:

Please Login or Register  to view this content.

note: above does not require Array entry; if you won't have blanks in Keyword ranges then both "x"& in the Match can be removed.