I am using the following formula to return strings from a Google Sheets range when the first instance in that range matches the value in cell A$1
from the lookup sheet:
=TEXTJOIN(CHAR(10),TRUE,INDEX('Task List'!$B$2:$H$100,MATCH(A$1,'Task List'!$A$2:$A$100,0),))
Each string that is returned from a particular column and row within 'Task List'!$B$2:$H$100
is separated by a line break.
I am struggling with how to adjust the formula in order to use it in another cell to return the 2nd instance from 'Task List'!$B$2:$H$100
of the value matching A$1
in 'Task List'!$A$2:$A$100
. After the 2nd instance, I would like to create additional formulas to return 3rd, 4th, 5th, etc. instances (one cell for each instance).
The icing on the cake would be to prepend the values in the first row of each of the columns of the Task List sheet to the corresponding strings returned from the same column.
In the screenshots provided, cells A2:B2
of the Kanban Board sheet show how the current formula works to return the first instance of the matching values. Cells C2:C3
show what the results would look like for the first two instances, as described above.
Completely agree with @z.. answer. Just to add for you to do more research, I suggest a REDUCE alternative that can do all the work in one cell. You can put in A2 this formula:
=REDUCE(TOROW(,1),A1:E1,LAMBDA(a,task,
IFNA(HSTACK(a,
IFNA(VSTACK(
BYROW(FILTER('Task List'!A:H,'Task List'!A:A=task),LAMBDA(each,TEXTJOIN(CHAR(10),1,each)))))))))
And you can get the full results (delete all other formulas):
It gets each value from the headers and filters the data, stacking the results vertically (with VSTACK) and getting each value joined by CHAR(10) as you previously did. Reduce allows to stack horizontally the process of each of the headers (with HSTACK)