Oct 12 2022 05:13 PM - edited Oct 12 2022 05:36 PM
Hello. I have 11 column with header which has a list of 8 to 13 words beneath them. I would like a formula that allows me to type one of the heading into cell A1 and have it automatically list every thing that is beneath the heading into A2, A3, etc. For example if my headers started in Cell D1 and were the months of the year, if I typed February in cell A1, all the item below February will appear in the cell below A1. Likewise if I type October in A1 the same results would occur respectively, replacing the previous content.
Can anyone help with that?
Oct 12 2022 06:38 PM - edited Oct 12 2022 06:47 PM
Solutionthere are multiple ways to do this. The best is if the data in columns D ... where you are pulling the data from are in a range formatted as a table. If that table is called Table1 then
=INDEX(Table1,,MATCH(A1,Table1[#Headers]))
or
=FILTER(Table1,Table1[#Headers]=A1,"")
if not then maybe:
=LET(c,MATCH(A1,D1:AA1,0), r, COUNTA(INDEX(D:AA,,c)), INDEX(D:AA,2,c):INDEX(D:AA,r,c))
or another option:
=LET(c,FILTER(DROP(D:AA,1),D1:AA1=A1,""),FILTER(c,c<>""))
Oct 13 2022 03:53 AM
Oct 12 2022 06:38 PM - edited Oct 12 2022 06:47 PM
Solutionthere are multiple ways to do this. The best is if the data in columns D ... where you are pulling the data from are in a range formatted as a table. If that table is called Table1 then
=INDEX(Table1,,MATCH(A1,Table1[#Headers]))
or
=FILTER(Table1,Table1[#Headers]=A1,"")
if not then maybe:
=LET(c,MATCH(A1,D1:AA1,0), r, COUNTA(INDEX(D:AA,,c)), INDEX(D:AA,2,c):INDEX(D:AA,r,c))
or another option:
=LET(c,FILTER(DROP(D:AA,1),D1:AA1=A1,""),FILTER(c,c<>""))