SOLVED
Home

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
Highlighted
zjohnson
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

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.