# How to Return Text from Column 1 if Text in Other Rows Match

Copper Contributor

# 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, the Tasks that are marked with an X in the top table.  The tasks can change depending on the week, and the number of tasks per day can also change, so I need the output to be able to return the task name in Column 1 for all rows marked with an X for each day of the week.  I hope this makes sense.

Thank You

6 Replies

# Re: How to Return Text from Column 1 if Text in Other Rows Match

Hello @56008769,
Use this array formula on B17

``=IFERROR(INDEX(\$A\$2:\$A\$13,SMALL(IF(B\$2:B\$13="X",ROW(\$A\$2:\$A\$13)-ROW(\$A\$2)+1),ROW(1:1))),"")``

# Re: How to Return Text from Column 1 if Text in Other Rows Match

As variant,

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.

# Re: How to Return Text from Column 1 if Text in Other Rows Match

you can use the filter formula

# Re: How to Return Text from Column 1 if Text in Other Rows Match

Practically same as above, but in one spill

``````=LET(
marks, \$B\$3:\$H\$14,
seq, SEQUENCE( COLUMNS( header ) ),
filtered, BYCOL(marks, LAMBDA(v, LAMBDA( FILTER(tasks, v <> "" ) ) ) ),
combined, IFNA( DROP( REDUCE("", seq, LAMBDA(a,v, HSTACK(a, INDEX(filtered,1,v)() ))),,1), "" ),
result
)``````

# Re: How to Return Text from Column 1 if Text in Other Rows Match

@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

# Re: How to Return Text from Column 1 if Text in Other Rows Match

With some reduction:

``````=DROP(
REDUCE(
"",
DayOfWeek,
LAMBDA(acc, v,
LET(
vector, XLOOKUP(v, DayOfWeek, tally, ""),
r, SUM(N(vector = "x")),