SOLVED

Creating A Sublist

%3CLINGO-SUB%20id%3D%22lingo-sub-370176%22%20slang%3D%22en-US%22%3ECreating%20A%20Sublist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-370176%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20create%20a%20sub-list%20from%20a%20master%20list%20-%20currently%20working%20on%20two%20formulas%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX('Client%20List'!B%3AB%2CMATCH(%22Chris%22%2C'Client%20List'!C%3AC%2C0))%3C%2FSTRONG%3E%20This%20formula%20will%20pull%20the%20first%20cell%20that%20matches%20the%20criteria%2C%20but%20will%20not%20populate%20the%20rest%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(INDEX('Client%20List'!B%3AB%2CSMALL(IF('Client%20List'!%5BAT%20Member%201%5D%3D%22Lindy%22%2CROW('Client%20List'!)-1)%2CROW(1%3A1))%2C2)%2C%22%22)%26nbsp%3B%3C%2FSTRONG%3E%20this%20formula%20will%20not%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-370176%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edynamic%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Elist%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESublist%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-382909%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20A%20Sublist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-382909%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F300987%22%20target%3D%22_blank%22%3E%40zjohnson%3C%2FA%3E%20%2C%20that%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(%0A%20%20INDEX(Table8%5BClient%20Name%5D%2C%0A%20%20AGGREGATE(15%2C6%2C1%2FISNUMBER(SEARCH(Table8%5BAT%20Member%201%5D%2C%24A%241))%2F%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ISNUMBER(SEARCH(Table8%5BAT%20Member%202%5D%2C%24A%241))%2F%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ISNUMBER(SEARCH(Table8%5BAT%20Member%203%5D%2C%24A%241))*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(ROW(Table8%5BAT%20Member%201%5D)-ROW(Table8%5B%5B%23Headers%5D%2C%5BAT%20Member%201%5D%5D))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROW()-ROW(%24A%241))%0A%20%20)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Efor%20the%20first%20section.%20Not%20sure%20what%20is%20the%20logic%20of%20the%20Master%20List%2C%20perhaps%20formula%20could%20be%20more%20flexible%20if%20know%20how%20you%20build%20sections.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-376749%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20A%20Sublist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BSee%20attached%2C%20the%20goal%20is%20to%20have%20the%20proper%20clients%20under%20the%20proper%20AT%20Member%20Names%20without%20having%20to%20manually%20do%20it%20on%20the%20%22Main%20View%22%20sheet%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-376595%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20A%20Sublist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376595%22%20slang%3D%22en-US%22%3EPlease%20provide%20a%20sample%20of%20the%20results%20you%20want%20to%20achieve%20in%20the%20Main%20View%20Sheet.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-376113%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20A%20Sublist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376113%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20help!%20However%2C%20i'm%20still%20struggling%20to%20get%20it%20to%20work.%20please%20see%20test%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-373783%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20A%20Sublist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-373783%22%20slang%3D%22en-US%22%3EIf%20you%20want%20to%20return%20all%20matches%20for%20%22Chris%22%2C%20which%20is%20in%20E1%2C%20you%20can%20use%20this%20formula%20in%20E2%3A%3CBR%20%2F%3E%3DIFERROR(INDEX('Client%20List'!B%3AB%2C%3CBR%20%2F%3EMATCH(1%2C%3CBR%20%2F%3EINDEX(('Client%20List'!C%3AC%3DE%241)*(COUNTIF(E%241%3AE1%2C'Client%20List'!B%3AB)%3D0)%2C0)%2C0))%2C%22%22)%3C%2FLINGO-BODY%3E
New Contributor

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 the criteria, but will not populate the rest

 

=IFERROR(INDEX('Client List'!B:B,SMALL(IF('Client List'![AT Member 1]="Lindy",ROW('Client List'!)-1),ROW(1:1)),2),"")  this formula will not work

5 Replies
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)),"")

@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

Best Response confirmed by zjohnson (New Contributor)
Solution

@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.