Forum Discussion
How to Return Text from Column 1 if Text in Other Rows Match
Hello, here is an example data set I am dealing with and what I am trying to find a formula for: In reality, I have about 85 tasks. In the second table, I want it to return for each day...
56008769 Just for fun, here's another approach for MS365 using some array reshaping functions:
=LET(
table, A1:H13,
vals, DROP(table, 1, 1),
test, vals <> "",
n, ROWS(vals),
v, SORTBY(
TOCOL(IF(test, TAKE(DROP(table, 1),, 1), "")),
TOCOL(IF({1}, SEQUENCE(, COLUMNS(vals)), vals)),,
TOCOL(IF(test, SEQUENCE(n))),
),
VSTACK(
DROP(TAKE(table, 1),, 1),
TAKE(WRAPCOLS(v, n), MAX(MMULT(EXPAND(1,, n, 1), N(test)), 1))
)
)
Adjust the table range reference as needed.
Sample Results