Golf team to individual matches

[Hobbesofmi]

Golf teams of 2 for 20 teams and each week the low handicap from each team will match play each other and higher will match play.

I have the roster with the handicaps on one tab and the matches on another. The team number is a merged two rows on the roster.

I've tried match, vlookup, sumif, etc. so I'm still back at square one.

		Match	Absents	Season	Team Standings
FLIGHT "A"	Gross	Hcp	Net	Individual	Team	Weeks Played	Total Strokes	Avg	New HCP	points this week	total points	PLACE
1	JERRY	45	6	39	2	4	8	348	43.50	6	12	215	5
JIM	A	5		2	8	342	42.75	5
2	MIKE	42	5	37	3	5	7	292	41.71	5	19	218	3
LARRY	44	7	37	2	8	353	44.13	7
3	JIM	44	10	34	1	3	9	431	47.89	10	31	225	1
JIM	39	2	37	2	8	310	38.75	2
4	RORY	47	6	41	0	0	10	437	43.70	6	22	215	5
DENNY	53	10	43	0	10	491	49.10	10
5	GLEN	42	8	34	3	5	7	319	45.57	8	32	182	10
JIM	50	12	38	2	8	403	50.38	12
6	WAYNE	46	6	40	3	6	7	307	43.86	6	28	220	2
JOE	46	11	35	3	7	344	49.14	11
7	TOM	45	12	33	0	0	10	500	50.00	11	29	216	4
RICH	47	7	40	0	10	447	44.70	7
8	MIKE	51	10	41	1	3	9	435	48.33	10	20	213	7
PAUL	A	9		2	8	379	47.38	9
9	TIM	47	14	33	2	3	8	418	52.25	13	25	200	9
DAN	46	7	39	1	9	406	45.11	7
10	****	45	9	36	1	1	9	418	46.44	8	21	208	8
ROY	56	15	41	0	10	549	54.90	15
FLIGHT "B"
11	TOM	43	11	32	1	1	9	442	49.11	10	28	209	2
RICK	49	12	37	0	10	513	51.30	12
12	KEVIN	48	10	38	0	0	10	479	47.90	10	19	190	5
KEVIN Jr.	53	13	40	0	10	518	51.80	13
13	CHUCK	48	9	39	0	2	10	474	47.40	9	15	205	3
JACK	56	17	39	2	8	453	56.63	17
14	SCOTT	49	11	38	0	1	10	494	49.40	11	20	210	1
RICHARD	57	12	45	1	9	465	51.67	13
15	MIKE	54	12	42	1	3	9	465	51.67	13	11	181	7
RAY	59	17	42	2	8	458	57.25	17
16	LES	56	15	41	1	2	9	494	54.89	15	12	176	9
STEVE	A	18		1	9	522	58.00	18
17	BILL	A	11		4	11	6	295	49.17	11	0	133	10
GARIE	A	19		7	3	185	61.67	19
18	BOB	59	15	44	3	4	7	388	55.43	16	18	178	8
WHIT	62	19	43	1	9	538	59.78	19
19	JIM	55	13	42	2	4	8	425	53.13	14	9	193	4
GARY	55	16	39	2	8	450	56.25	16
20	JOHN	51	17	34	0	1	9	504	56.00	16	21	189	6
NAVID	55	15	40	1	6	326	54.33	15

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:7716;width:158pt" width="211"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="width:48pt" span="8" width="64"> </colgroup><tbody>
</tbody>

MATCH NO.	1			Team	8	Team	11				5:00					FRONT	NINE
		BLUE	506	380	358	305	392	361	232	174	523	3231
		WHITE	493	365	342	300	374	346	220	169	503	3112			INDIVIDUAL	TEAM #	8
		RED	473	345	296	296	344	320	207	162	397	2840			POINTS	POINTS
ABS.		POINTS	0	1	2	1	2	2	1	1	1			2	13	29
		INDV. HOLE H'CAP	0	0	0	0	0	0	0	0	0
	PAUL	47.38	0		7	4	6	6	4	5	7	4	4	47	8	39
	TOM	49.11	0		5	4	8	6	7	6	7	4	5	52	9	43
		INDV. HOLE H'CAP	0	0	0	0	0	0	0	0	1			0	7
		POINTS	2	1	0	1	0	0	1	1	1
		Average	Tot. Abs.	HOLE	1	2	3	4	5	6	7	8	9	OUT	H'CAP.	NET	INDIVIDUAL	Team #	11
		HANDICAP	6	9	7	5	4	3	2	8	1	POINTS	POINTS
ABS.		POINTS	0	2	2	2	2	1	1	2	2			2	16	11
		INDV. HOLE H'CAP	0	0	0	0	0	0	0	0	0
	MIKE	48.33	0		8	4	6	4	5	4	7	4	6	48	9	39
	RICK	51.30	0		6	6	7	5	6	4	7	5	8	54	10	44
		INDV. HOLE H'CAP	0	0	0	0	0	0	0	0	1			0	4
		POINTS	2	0	0	0	0	1	1	0	0
		PAR	5	3	5	4	4	4	4	3	4	36

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:6363;width:131pt" width="174"> <col style="width:48pt" span="18" width="64"> </colgroup><tbody>
</tbody>

 

[Hobbesofmi]

The first name formula is =INDEX(Sheet3!$C$8:$C$55,MATCH(Sheet2!$G$1,Sheet3!$B$8:$B$55,0))

I found one way to do it the second name with INDEX, SMALL and ROW that was the same formula in my last update: {=INDEX(Sheet3!C9:C56,SMALL(IF(Sheet3!B9:B56=I1,ROW(Sheet3!$B$8:$B$55)-ROW(Sheet3!$B$8)+1),2))}

The {} is the key that tells the formula that it's not a value. You can't type the {} in the formula but you must do a CTRL SHIFT ENTER when in the formula. Saving in once you do it then you can change the team number on the top and it auto puts in both team members in the right spot. You can do it for all 10 matches for 20 weeks ahead of time and as things changes I've changed the area that it's looking and it's done.