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...
As variant,
=LET(day,B2,tasks,BYROW(B3:B14,LAMBDA(r,IF(r="X",OFFSET(r,0,COLUMN($A$3)-COLUMN(B2),1,1),""))),FILTER(VSTACK(day,tasks),VSTACK(day,tasks)<>""))
You can drag the formula to the right to complete all days of the week. If the number of tasks may increase you can just set the ranges to row 100 or whatever in order to make sure all tasks are covered. If you are on Excel 365, this will automatically get your tasks updated if any changes.
See attached document.