Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

How do I get excel to auto-populate a list based off of which header is type in another cell

Brass Contributor

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?

2 Replies
best response confirmed by Asparagus (Brass Contributor)
Solution

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
1 best response

Accepted Solutions
best response confirmed by Asparagus (Brass Contributor)
Solution

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<>""))

 

 

View solution in original post