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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies