Identifying matches between multiple columns

Hi Jeff,

I assume what text in column A "Biggest Strengths" is included in related cell in column C "All Strengths They Chose" as substring. Thus we only need to compare columns B and C.

Two identical possible solutions.

If you have Excel 2016 where TEXTJOIN function is available when we may add to column E array formula (Ctrl+Shift+Enter)

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH($B2,$C$2:$C$48)),$D$2:$D$48,""))

It checks if text in cell of column B appears somethere in column C and combine related ID:s (column D) into one string. That is in Sheet1 of the attached file.

For previous version of Excel that's bit more complicated (see Sheet2). We will find matched ID:s one by one.

First match is in column E

=INDEX($D$2:$D$48,MATCH(1,--ISNUMBER(SEARCH($B2,$C$2:$C$48)),0))

, drag E2 down till end of the range.

In next columns we shall exclude already found ID:s. To do that we start search from the position next to previously founded ID in previous column shifting start of the search by OFFSET: in column F also array formula

=IFERROR(INDEX(OFFSET($D$2:$D$48,MATCH(E2,$D$2:$D$48,0),0),MATCH(1,--ISNUMBER(SEARCH($B2,OFFSET($C$2:$C$48,MATCH(E2,$D$2:$D$48,0),0))),0)),"")

drag F2 down till end of the range and after that entire column F (more exactly F2:F48 in your case) to the right till all cells in the column becomes empty.