Forum Discussion
Solved
Creating A Sublist
Trying to create a sub-list from a master list - currently working on two formulas: =INDEX('Client List'!B:B,MATCH("Chris",'Client List'!C:C,0)) This formula will pull the first cell that matches...
zjohnson , that could be like
=IFERROR( INDEX(Table8[Client Name], AGGREGATE(15,6,1/ISNUMBER(SEARCH(Table8[AT Member 1],$A$1))/ ISNUMBER(SEARCH(Table8[AT Member 2],$A$1))/ ISNUMBER(SEARCH(Table8[AT Member 3],$A$1))* (ROW(Table8[AT Member 1])-ROW(Table8[[#Headers],[AT Member 1]])), ROW()-ROW($A$1)) ),"")for the first section. Not sure what is the logic of the Master List, perhaps formula could be more flexible if know how you build sections.
If you want to return all matches for "Chris", which is in E1, you can use this formula in E2:
=IFERROR(INDEX('Client List'!B:B,
MATCH(1,
INDEX(('Client List'!C:C=E$1)*(COUNTIF(E$1:E1,'Client List'!B:B)=0),0),0)),"")
=IFERROR(INDEX('Client List'!B:B,
MATCH(1,
INDEX(('Client List'!C:C=E$1)*(COUNTIF(E$1:E1,'Client List'!B:B)=0),0),0)),"")
Twifoo Thanks for your help! However, i'm still struggling to get it to work. please see test file.
- Please provide a sample of the results you want to achieve in the Main View Sheet.
Twifoo See attached, the goal is to have the proper clients under the proper AT Member Names without having to manually do it on the "Main View" sheet
zjohnson , that could be like
=IFERROR( INDEX(Table8[Client Name], AGGREGATE(15,6,1/ISNUMBER(SEARCH(Table8[AT Member 1],$A$1))/ ISNUMBER(SEARCH(Table8[AT Member 2],$A$1))/ ISNUMBER(SEARCH(Table8[AT Member 3],$A$1))* (ROW(Table8[AT Member 1])-ROW(Table8[[#Headers],[AT Member 1]])), ROW()-ROW($A$1)) ),"")for the first section. Not sure what is the logic of the Master List, perhaps formula could be more flexible if know how you build sections.