• 382K Members
• 6,099 Online
• 400K Conversations
SOLVED

Highlighted
New Contributor

# 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

5 Replies

# Re: Creating A Sublist

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

# Re: Creating A Sublist

@Twifoo Thanks for your help! However, i'm still struggling to get it to work. please see test file.

# Re: Creating A Sublist

Please provide a sample of the results you want to achieve in the Main View Sheet.

# Re: Creating A Sublist

@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

# Re: Creating A Sublist

@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))*