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

Twifoo's avatar
Twifoo
Silver Contributor
Mar 19, 2019
Please provide a sample of the results you want to achieve in the Main View Sheet.
  • zjohnson's avatar
    zjohnson
    Copper Contributor
    Mar 19, 2019

    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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      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.

Resources