Excel's FILTER function can be used to generate (by entering the formula once in a single cell) results in multiple cells, by searching for a value in one column, and returning the data from the complete range where there's a hit for that value.
So in this example, the FILTER function entered in cell G2, returns from the range A2:D8, those entries where 'Tom' is listed as the sales rep in column B.
If you need to search for where a word or phrase appears in multiple cells in a row, you can use SEARCH nested in an ISNUMBER formula, and return the full contents of any cell which contains the searched for string. [See the explanation for this formula in the Tip of the Night for September 20, 2021] So in this example, we want to see which cells in the range from columns B to D contain references to the painter named in column J.
The formula is composed by searching for the value in cell J1, on the second row between columns B and D, and then filtering down the results from that range.
=IFERROR(FILTER($B2:$D2,ISNUMBER(SEARCH(J$1,$B2:$D2))),"")
The IFERROR function has the effect of excluding the '#CALC!' which would result if the FILTER function did not find a result. Entered this way, the FILTER function will return multiple hits from the cited range in multiple cells to the right of the column in which it is entered. When the complete formula is copied to search for strings entered at cells K1 and L1 (we use absolute references for columns B and D by entering dollar signs, so the complete formula points to the correct array) however a #SPILL! error will result as the data is overwritten.
To avoid this problem, we nest the formula in a TEXTJOIN function like this, so each hit in the range from column B to column gets entered in a single cell.
=IFERROR(TEXTJOIN("; ",TRUE,FILTER($B2:$D2,ISNUMBER(SEARCH(J$1,$B2:$D2))),""),"")
We can collect the results returned for all 10 rows by then using a simple TEXTJOIN function, as described in the post for June 29, 2024.
