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? 

 

LuckyMonkey_0-1718310717555.png

 

2 Replies

@LuckyMonkey 

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.