Apply one formula across multiple columns in one cell

Copper Contributor

I have a workbook with Titles, Tests, and Results across columns. I need cell T3 to return the Title if the result is Yes. Each Title should be on a new line. Right now I'm using 6 separate If functions, but in my real scenario, there are many more columns. Is there a way to simplify my formula so that one function can apply to many columns, but keep the results in the same cell? 




2 Replies


Use a formula like this:


=TEXTJOIN(CHAR(10), TRUE, FILTER(A1:S1, C3:U3="Yes", ""))


You can expand the ranges as needed. Please note that the second range in FILTER must have the same number of columns as the first range. It is offset 2 rows down and 2 columns to the right from the first range.