Forum Discussion
How do I get excel to auto-populate a list based off of which header is type in another cell
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?
there 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<>""))
2 Replies
there 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<>""))- This worked amazingly thank you very much. I use the first table formula example that you posted. I will try the others soon as I finish this task.
cheers
