Forum Discussion

zjohnson's avatar
zjohnson
Copper Contributor
Mar 15, 2019
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 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

dynamic
excel
formula
List
Sublist
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 22, 2019

    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.

5 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Mar 16, 2019
    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)),"")

Resources