list items from one table in another table based on value in first table

[Karin S]

I have a table of people.
Columns are
First name, last name, address, team #, full name, etc

There are about 50 people in the list and five teams total, so "team #" column has a number (1,2,3,4 or 5) in it.

I would like to extract a list that has five columns across the top (1, 2, 3, 4, 5) and lists all the team members of each particular team underneath.

So, if I change a person's team # in the "main" list, the "extracted" list will update accordingly.

It seems like this should be so simple but I can't think of a function/formula that will do it. (I tried PivotTables first, but they would only give me "count" or "sum" etc of the names, not the list of names themselves)

If I use LOOKUP, I can only find the first one. Don't know how to find (and list) the second, third, etc... And I'm done formulas with SUMPRODUCT, array (CSE), etc, before but I just can't figure this one out.

Thanks for any help...
Karin

 

[Claus Busch]

Hi Karin,

Am Tue, 17 Sep 2013 11:52:06 -0700 (PDT) schrieb Karin S:

I have a table of people.
Columns are
First name, last name, address, team #, full name, etc

There are about 50 people in the list and five teams total, so "team #" column has a number (1,2,3,4 or 5) in it.

I would like to extract a list that has five columns across the top (1, 2, 3, 4, 5) and lists all the team members of each particular team underneath.

your table with the people is in Sheet1
Then in sheet2 for team # 1:
=INDEX(Sheet1!E:E,SMALL(IF(Sheet1!D$1$100=1,ROW($1:$100)),ROW(A1)))
and enter the array formula with CTRL+Shift+Enter and copy down till you
get an error.


Regards
Claus B.

 

[Karin S]

=INDEX(Sheet1!E:E,SMALL(IF(Sheet1!D$1$100=1,ROW($1:$100)),ROW(A1)))

and enter the array formula with CTRL+Shift+Enter and copy down till you get an error.
Regards

Claus B.

Wow! Beautiful!

I saw some references to INDEX() and SMALL() when I was doing a search before I posted my question but had no idea how to use them myself. This works wonderfully!! Now I have to finish dissecting it so I understand WHY it works... : )

Danke vielmals!
Karin