Index&Match Array Formula to Return Multiple Matches not working when looking up numbers

[loto]

Hi everyone

I've been trying to create a spreadsheet that tracks our golf group (boring I know!). And I have a table ranking the players that have won the most match points, made the most pars, birdies, etc, over the course of the week. I can use INDEX and MATCH functions to find the highest ranked player and this works well when there is only 1 winner - but how can I get the formula to return multiple matches where there is more than 1 player with the same score? Is there a way to get the formula to list the names in one cell (C36, D36, etc) separated by columns or do I have to copy the formula down the column to list all matching references? The only other problem is that for 'Lost Balls' the winner is the one with the lowest score (all the others the highest score wins) - but I assume I can solve that with MIN function.

Any help would be much appreciated. I did have one formula partially working but it seems to break when I lookup numbers rather than text!

Example:

	A	B	C	D	E	G	H
1	MVP Rank	Team	Match Points	Pars	Birdies	Eagles	Lost Balls
2	1	Player 1	5	30	15	5	4
3	1	Player 2	5	4	0	0	3
4	23	Player 3	0	10	0	0	4
5	23	Player 4	0	15	0	0	4
6	11	Player 5	2.5	20	0	0	5
7	11	Player 6	2.5	25	0	0	4
8	11	Player 7	2.5	20	0	0	2
9	11	Player 8	2.5	30	0	0	1
10	1	Player 9	5	25	0	0	0
11	1	Player 10	5	30	0	0	12
12	23	Player 11	0	25	0	0	34
13	23	Player 12	0	30	0	0	12
14	23	Player 13	0	25	0	0	5
15	23	Player 14	0	30	0	0	13
16	11	Player 15	2.5	45	20	0	4
17	11	Player 16	2.5	45	20	0	1
18	23	Player 17	0	8	9	5	2
19	23	Player 18	0	35	0	5	3
20	1	Player 19	5	60	10	0	4
21	1	Player 20	5	40	5	0	5
22	11	Player 21	2.5	40	10	5	1
23	11	Player 22	2.5	45	10	0	0
24	11	Player 23	2.5	20	0	5	5
25	11	Player 24	2.5	50	10	0	6
26	23	Player 25	0	30	0	0	4
27	23	Player 26	0	30	0	0	7
28	1	Player 27	5	70	15	0	14
29	1	Player 28	5	35	5	5	8
30	1	Player 29	5	30	0	0	2
31	1	Player 30	5	90	0	0	2
32	11	Player 31	2.5	45	20	0	3
33	11	Player 32	2.5	50	20	0	8
34
35		RESULTS	MVP	Pars	Birdies	Eagles	Lost Balls
36		WINNER	Player Names	Player Names	Player Names	Player Names	Player Names

<tbody>
</tbody>

Thanks!!

 

[AhoyNC]

Here's one way.
These are array formulas and must be entered with CTRL-SHIFT-ENTER (command-return on MAC).
Also, the IFERROR function is only available in Excel 2010 or later.

Formula in C36 can be copied down and across to column F.
Formula in G36 is the same only changing MAX to MIN (copy this formula down as needed).

Excel Workbook
	A	B	C	D	E	F	G
1	MVP Rank	Team	Match Points	Pars	Birdies	Eagles	Lost Balls
2	1	Player 1	5	30	15	5	4
3	1	Player 2	5	4	0	0	3
4	23	Player 3	0	10	0	0	4
5	23	Player 4	0	15	0	0	4
6	11	Player 5	2.5	20	0	0	5
7	11	Player 6	2.5	25	0	0	4
8	11	Player 7	2.5	20	0	0	2
9	11	Player 8	2.5	30	0	0	1
10	1	Player 9	5	25	0	0	0
11	1	Player 10	5	30	0	0	12
12	23	Player 11	0	25	0	0	34
13	23	Player 12	0	30	0	0	12
14	23	Player 13	0	25	0	0	5
15	23	Player 14	0	30	0	0	13
16	11	Player 15	2.5	45	20	0	4
17	11	Player 16	2.5	45	20	0	1
18	23	Player 17	0	8	9	5	2
19	23	Player 18	0	35	0	5	3
20	1	Player 19	5	60	10	0	4
21	1	Player 20	5	40	5	0	5
22	11	Player 21	2.5	40	10	5	1
23	11	Player 22	2.5	45	10	0	0
24	11	Player 23	2.5	20	0	5	5
25	11	Player 24	2.5	50	10	0	6
26	23	Player 25	0	30	0	0	4
27	23	Player 26	0	30	0	0	7
28	1	Player 27	5	70	15	0	14
29	1	Player 28	5	35	5	5	8
30	1	Player 29	5	30	0	0	2
31	1	Player 30	5	90	0	0	2
32	11	Player 31	2.5	45	20	0	3
33	11	Player 32	2.5	50	20	0	8
34	*	*	*	*	*	*	*
35	*	RESULTS	MVP	Pars	Birdies	Eagles	Lost Balls
36	*	WINNER	Player 1	Player 30	Player 15	Player 1	Player 9
37	*	*	Player 2	*	Player 16	Player 17	Player 22
38	*	*	Player 9	*	Player 31	Player 18	*
39	*	*	Player 10	*	Player 32	Player 21	*
40	*	*	Player 19	*	*	Player 23	*
41	*	*	Player 20	*	*	Player 28	*
42	*	*	Player 27	*	*	*	*
43	*	*	Player 28	*	*	*	*
44	*	*	Player 29	*	*	*	*
45	*	*	Player 30	*	*	*	*
Sheet

 

[loto]

@AhoyNC - it's official you're a genius It works perfectly!

Thank-you so much - wish I'd asked you last night instead of trying to do it at 2 a.m.!

And now I can see what I was doing wrong!

 

[AhoyNC]

You're welcome. Thanks for the feedback. I wouldn't have been awake at 2 am.