multiple returns from a table

[Hattie]

Excel 365

I have a list of people in a sheet.
The columns are name, team, points.

There are 2 teams.

I want to make 2 new sheets (one team per sheet) with the members of each team and the appropriate points of each member next to them.

I tried vlookup but that only brings back the first match of each team but i want every member of the team. One per line.

Could anyone point me in the right direction?

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
Maybe something like

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1	Name	Team	Points		Team 1	Name	Points
2	A	Team 2	33			C	89
3	B	Team 2	50			D	82
4	C	Team 1	89			F	61
5	D	Team 1	82			I	26
6	E	Team 2	19			L	69
7	F	Team 1	61			O	60
8	G	Team 2	93			Q	32
9	H	Team 2	99			R	23
10	I	Team 1	26			T	95
11	J	Team 2	78
12	K	Team 2	30
13	L	Team 1	69
14	M	Team 2	96
15	N	Team 2	50
16	O	Team 1	60
17	P	Team 2	42
18	Q	Team 1	32
19	R	Team 1	23
20	S	Team 2	69
21	T	Team 1	95
Main

Cell Formulas
Range	Formula
F2:G10	F2	=FILTER(FILTER(A2:C21,B2:B21=E1),COUNTIF(F1:G1,A1:C1))
Dynamic array formulas.

 

[Hattie]

Wow, Yes. Perfect

Exactly what I wanted.

I'm not sure how this magic works. Yet

But I have been able to amended it so the team is in column A, names in column B.

but I will go and read about these functions and find out.

thank you very much. I owe you a beer

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Hattie]

thank you.

I was only excpecting a look at this function.

I worked example made my understanding a lot easier.

take care

 

[Fluff]

My pleasure

 

[Hattie]

i have adopted your code into another project, one at work.

can the filter function only select one source string?

if i make the search [ B2:B21=E1 ] point to a dropdown box in my pivot table it works if i select one item but if i select 2 items the dropdown box obviously shows the text multiply items

and this breaks the code

it doesn't bring back members from both of the teams selected, because they are marked as team 1 or 2 and not as multiply item.

is there a way to make it do the search twice and bring back two sets of data?

or am i barking up the wrong tree here?

thanks

 

[Fluff]

You have already started a new thread for this question, so you need to stick to that thread.

 

[Hattie]

ok thanks