Excel - return cell value from each row if text is found in cell range in same row?-CodePudding

I have an Excel spreadsheet where I have 3 columns with names in it and the rows are simply named Row1-Row9.

I want to list all rows where a name is found to be any part of a cell (not exact match).

So, I'm searching for the name and if found in a row, return the A cell value from that row.

I've made a sample in the image provided. I've looked at a lot of different "if" formulas for excel but could not find an answer for this.

I can manually put each name into the formula for each unique person, but just cannot figure out how to return the A1 cell values per row when looking for a name.

CodePudding user response：

Give a try on below formula-

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(A14,B2:D8)),A2:A8,""))

CodePudding user response：

=TEXTJOIN(", ",1,FILTER(A2:A10,SIGN(MMULT(--ISNUMBER(SEARCH(A14,C2:E10)),SEQUENCE(COLUMNS(C2:E10),,,0))),"No matches"))

This requires Office 365.

Where A14 is the search value, C2:E10 is the search range and A2:A10 is the return range.