A simple addition breaks my formula and my brain!

[Tomeegee]

MrExcel has saved me in the past my golf tourney scoring spreadsheet. It has worked well. However, as I changed a course name, some formulas that worked smoothly now give an error on just that course. The new course is "Sanctuary" in the top image. The bottom image shows how all other courses deliver the proper value. The formula is =INDEX(($B$9:$E$25,$B$28:$E$44,$B$47:$E$63,$B$66:$E$82),MATCH($B$3,$B$9:$B$25,0),IF($B5="Front",3,4),MATCH($I$3,$O$2:$O$6))

Maybe it's some dead simple? I've compared the formulas to the one used with the previous course, and nothing changed. Thanks in advance!

 

[awoohaw]

Some of the forum members (me, for one) would need to recreate your workbook. That is a lot of work.
Mr. Excel has a tool xl2bb add in (Link below) that allows you to post a mini workbook.

In addition to all the work, we could also make errors in your data (and assumptions).

Please help the forum help you, and post a mini workbook. If for some reason you can't the please post the data as a table and not an image.

 

[Fluff]

You need to specify an exact match for the 2nd match function as well as changing the range to start at O3

=INDEX(($B$9:$E$25,$B$28:$E$44,$B$47:$E$63,$B$66:$E$82),MATCH($B$3,$B$9:$B$25,0),IF($B5="Front",3,4),MATCH($I$3,$O$3:$O$6,0))

Although I'm not sure it's actually giving the correct results

 

[Tomeegee]

You need to specify an exact match for the 2nd match function as well as changing the range to start at O3

=INDEX(($B$9:$E$25,$B$28:$E$44,$B$47:$E$63,$B$66:$E$82),MATCH($B$3,$B$9:$B$25,0),IF($B5="Front",3,4),MATCH($I$3,$O$3:$O$6,0))

Although I'm not sure it's actually giving the correct results

It did indeed work on the page I sent. But when I brought the formula out to the worksheet where the scorecards are (S22_90):
=INDEX((S22_100!$B$9:$E$25,S22_100!$B$28:$E$44,S22_100!$B$47:$E$63,S22_100!$B$66:$E$82),MATCH(B3,S22_100!$B$9:$B$25,0),IF($B5="Front",3,4),MATCH($I3,S22_90!$O$2:$O$6,0))
I encountered the same problem.

problem formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K		M		O	P
1
2		Player		HCP						Player	Hcp				Courses	Side
3		Tom G		9					Sanctuary	Tom G	19.8				PineLakes	FRONT
4										Brian A	19.7				Oleander	BACK
5		FRONT								Jody B	22.6				IndianMound
6										Wally B	20				Sanctuary
7										Rich C	13.3
8		Player	Side	Front	Back		PineLakes			Joel C	33.2
9		Tom G		20	21					Jim G	14.6
10		Brian A		9	8					Ray L	25.9
11		Jody B		10	10					Jay M	7.6
12		Wally B		9	8					Mark M	27.6
13		Rich C		5	6					Ken R	16.4
14		Joel C		13	13					Mike S	22.3
15		Jim G		6	6					Steven S	15.7
16		Ray L		10	10					Rob T	9.3
17		Jay M		4	3					Dean V	14.6
18		Mark M		11	11					Bruce W	12.8
19		Ken R		7	7					Course	0
20		Mike S		8	8
21		Steven S		7	6
22		Rob T		4	3
23		Dean V		6	6
24		Bruce W		5	5
25		Course		0	0
26
27		Player	Side	Front	Back		Oleander
28		Tom G		15	18
29		Brian A		9	9
30		Jody B		10	10
31		Wally B		9	9										9
32		Rich C		6	6
33		Joel C		13	14
34		Jim G		6	7
35		Ray L		10	10
36		Jay M		4	4
37		Mark M		11	11
38		Ken R		7	7
39		Mike S		8	9
40		Steven S		7	7
41		Rob T		4	4
42		Dean V		6	7
43		Bruce W		5	6
44		Course		0	0
45
46		Player	Side	Front	Back		IndianMound
47		Tom G		5	11
48		Brian A		9	9
49		Jody B		10	10
50		Wally B		9	9
51		Rich C		6	6
52		Joel C		13	13
53		Jim G		6	6
54		Ray L		10	10
55		Jay M		3	3
56		Mark M		11	11
57		Ken R		7	7
58		Mike S		8	8
59		Steven S		7	7
60		Rob T		4	4
61		Dean V		6	6
62		Bruce W		5	5
63		Course		0	0
64
65		Player	Side	Front	Back		Sanctuary
66		Tom G		9	17
67		Brian A		10	10
68		Jody B		11	11
69		Wally B		10	10
70		Rich C		6	7
71		Joel C		14	15
72		Jim G		7	7
73		Ray L		11	12
74		Jay M		5	5
75		Mark M		11	12
76		Ken R		8	8
77		Mike S		9	10
78		Steven S		7	8
79		Rob T		4	5
80		Dean V		7	7
81		Bruce W		6	6
82		Course		0	0
S22_90 (2)

Cell Formulas
Range	Formula
D3	D3	=INDEX(($B$9:$E$25,$B$28:$E$44,$B$47:$E$63,$B$66:$E$82),MATCH($B$3,$B$9:$B$25,0),IF($B5="Front",3,4),MATCH($I$3,$O$3:$O$6,0))
B8:B25,B65:B82,B46:B63,B27:B44	B8	=$J$2:$J$19
D9:E9	D9	='9 Hole Handicaps'!H6
D10:E10	D10	='9 Hole Handicaps'!H12
D11:E11	D11	='9 Hole Handicaps'!H18
D12:E12	D12	='9 Hole Handicaps'!H24
D13:E13	D13	='9 Hole Handicaps'!H30
D14:E14	D14	='9 Hole Handicaps'!H36
D15:E15	D15	='9 Hole Handicaps'!H42
D16:E16	D16	='9 Hole Handicaps'!H48
D17:E17	D17	='9 Hole Handicaps'!H54
D18:E18	D18	='9 Hole Handicaps'!H60
D19:E19	D19	='9 Hole Handicaps'!H66
D20:E20	D20	='9 Hole Handicaps'!H72
D21:E21	D21	='9 Hole Handicaps'!H78
D22:E22	D22	='9 Hole Handicaps'!H84
D23:E23	D23	='9 Hole Handicaps'!H90
D24:E24	D24	='9 Hole Handicaps'!H96
K3	K3	='9 Hole Handicaps'!B4
K4	K4	='9 Hole Handicaps'!B10
K5	K5	='9 Hole Handicaps'!B16
K6	K6	='9 Hole Handicaps'!B22
K7	K7	='9 Hole Handicaps'!B28
K8	K8	='9 Hole Handicaps'!B34
K9	K9	='9 Hole Handicaps'!B40
K10	K10	='9 Hole Handicaps'!B46
K11	K11	='9 Hole Handicaps'!B52
K12	K12	='9 Hole Handicaps'!B58
K13	K13	='9 Hole Handicaps'!B64
K14	K14	='9 Hole Handicaps'!B70
K15	K15	='9 Hole Handicaps'!B76
K16	K16	='9 Hole Handicaps'!B82
K17	K17	='9 Hole Handicaps'!B88
K18	K18	='9 Hole Handicaps'!B94
D28:E28	D28	='9 Hole Handicaps'!K6
D29:E29	D29	='9 Hole Handicaps'!K12
D30:E30	D30	='9 Hole Handicaps'!K18
D31:E31	D31	='9 Hole Handicaps'!K24
D32:E32	D32	='9 Hole Handicaps'!K30
D33:E33	D33	='9 Hole Handicaps'!K36
D34:E34	D34	='9 Hole Handicaps'!K42
D35:E35	D35	='9 Hole Handicaps'!K48
D36:E36	D36	='9 Hole Handicaps'!K54
D37:E37	D37	='9 Hole Handicaps'!K60
D38:E38	D38	='9 Hole Handicaps'!K66
D39:E39	D39	='9 Hole Handicaps'!K72
D40:E40	D40	='9 Hole Handicaps'!K78
D41:E41	D41	='9 Hole Handicaps'!K84
D42:E42	D42	='9 Hole Handicaps'!K90
D43:E43	D43	='9 Hole Handicaps'!K96
O31	O31	=INDEX(($B$9:$E$25,$B$28:$E$44,$B$47:$E$63,$B$66:$E$82),MATCH($B$3,$B$9:$B$25,0),IF($B5="Front",3,4),MATCH($I$3,$O$3:$O$6,0))
D47:E47	D47	='9 Hole Handicaps'!E6
D48:E48	D48	='9 Hole Handicaps'!E12
D49:E49	D49	='9 Hole Handicaps'!E18
D50:E50	D50	='9 Hole Handicaps'!E24
D51:E51	D51	='9 Hole Handicaps'!E30
D52:E52	D52	='9 Hole Handicaps'!E36
D53:E53	D53	='9 Hole Handicaps'!E42
D54:E54	D54	='9 Hole Handicaps'!E48
D55:E55	D55	='9 Hole Handicaps'!E54
D56:E56	D56	='9 Hole Handicaps'!E60
D57:E57	D57	='9 Hole Handicaps'!E66
D58:E58	D58	='9 Hole Handicaps'!E72
D59:E59	D59	='9 Hole Handicaps'!E78
D60:E60	D60	='9 Hole Handicaps'!E84
D61:E61	D61	='9 Hole Handicaps'!E90
D62:E62	D62	='9 Hole Handicaps'!E96
D66:E66	D66	='9 Hole Handicaps'!B6
D67:E67	D67	='9 Hole Handicaps'!B12
D68:E68	D68	='9 Hole Handicaps'!B18
D69:E69	D69	='9 Hole Handicaps'!B24
D70:E70	D70	='9 Hole Handicaps'!B30
D71:E71	D71	='9 Hole Handicaps'!B36
D72:E72	D72	='9 Hole Handicaps'!B42
D73:E73	D73	='9 Hole Handicaps'!B48
D74:E74	D74	='9 Hole Handicaps'!B54
D75:E75	D75	='9 Hole Handicaps'!B60
D76:E76	D76	='9 Hole Handicaps'!B66
D77:E77	D77	='9 Hole Handicaps'!B72
D78:E78	D78	='9 Hole Handicaps'!B78
D79:E79	D79	='9 Hole Handicaps'!B84
D80:E80	D80	='9 Hole Handicaps'!B90
D81:E81	D81	='9 Hole Handicaps'!B96
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'S22_90 (2)'!Player	='S22_90 (2)'!$J$3:$J$19	B65, B46, B27, B8

Cells with Data Validation
Cell	Allow	Criteria
O2:O6	List	=$O$2:$O$6
G8	List	=$O$2:$O$6
I3	List	=$O$2:$O$6
G46	List	=$O$2:$O$6
G27	List	=$O$2:$O$6
G65	List	=$O$2:$O$6
B5	List	=Side
I5	List	=Dates
B3	List	=Player
J2:J19	List	=Player

 

[MARK858]

You need to select the range that you want to copy before you click the mini-sheet button

 

[Fluff]

You've changed the range in the last Match back to O2 rather than O3

 

[Tomeegee]

Some of the forum members (me, for one) would need to recreate your workbook. That is a lot of work.
Mr. Excel has a tool xl2bb add in (Link below) that allows you to post a mini workbook.

In addition to all the work, we could also make errors in your data (and assumptions).

Please help the forum help you, and post a mini workbook. If for some reason you can't the please post the data as a table and not an image.

Let me try Mini-sheet here

You need to select the range that you want to copy before you click the mini-sheet button

Thanks. Rather clumsy with Xl2.

 

[Tomeegee]

You've changed the range in the last Match back to O2 rather than O3

Thanks much. I finally got it right and am able to use the formula throughout the doc.

I have one other page which doesn't prevent me from getting data from the page, but I can't understand why it's returning what it is. This page is set up to show values if a Player is a woman "Gina M" or just a "Player". I don't get why all courses return #NAME? in the "if with Player" row. The values that should be there do show up in other sections of my workbook. But here they do this. Also, if the course PineLakes is chosen, all the if With Gina M values become FALSE. Arrays are way above my pay grade at the moment, so figuring this one out has been burdensome.

problem formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2		Player
3		Player	M				Golf Course:		PineLakes
4
5		FRONT
6
7		HOLE	1	2	3	4	5	6	7	8	9	Total
8		Score Card Par	5	4	3	4	4	3	5	4	4	36
9		Hole Handicap	9	13	15	7	3	17	1	11	5
10
11			1	2	3	4	5	6	7	8	9		10	11	12	13	14	15	16	17	18
12
13		PineLakes	5	4	3	4	4	3	5	4	4	36	4	5	4	4	3	5	4	3	4	36	72
14		Men's Handicap	9	13	15	7	3	17	1	11	5		8	2	10	12	18	4	16	14	6
15		Ladies Handicap	3	7	15	9	17	13	5	11	1		18	6	14	10	12	4	16	8	2
16
17		Oleander	4	4	3	5	4	5	4	3	4	36	4	5	4	3	4	5	3	4	4	36	72
18		Men's Handicap	13	5	15	1	3	7	11	17	9		10	4	2	16	12	8	18	14	6
19		Ladies Handicap	13	5	15	1	3	7	11	17	9		10	4	2	16	12	8	18	14	6
20
21		IndianMound	4	3	5	4	4	3	5	4	4	36	5	4	3	4	4	4	3	5	4	36	72
22		Men's Handicap	11	17	1	13	7	15	3	9	5		4	14	18	6	12	10	16	2	8
23		Ladies Handicap	5	15	1	13	11	17	3	9	7		4	14	18	6	12	10	16	2	8
24
25		Sanctuary	4	4	5	3	4	3	4	5	4	36	4	4	4	3	4	5	3	5	4	36	72
26		Men's Handicap	3	13	15	11	7	17	9	5	1		14	6	10	18	4	12	16	8	2
27		Ladies Handicap	1	7	11	17	5	9	13	15	3		14	6	12	18	8	10	16	2	4
28
29
30		If with Player	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?		{=IF(AND(B3=Player,$B5="FRONT",$I3="PineLakes"),$C$14:$K$14,IF(AND($B5="BACK",$I3="PineLakes"),$M$14:$U$14,IF(AND($B5="FRONT",$I3="Oleander"),$C$18:$K$18,IF(AND($B5="BACK",$I3="Oleander"),$M$18:$U$18,IF(AND($B5="FRONT",$I3="IndianMound"),$C$22:$K$22,IF(AND($B5="BACK",$I3="IndianMound"),$M$22:$U$22,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$26:$K$26,IF(AND($B5="BACK",$I3="Sanctuary"),$M$26:$U$26))))))))}
31		if With Gina M	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		{=IF(AND(B3="Gina M",$B5="FRONT",$I3="PineLakes"),$C$15:$K$15,IF(AND($B5="BACK",$I3="PineLakes"),$M$15:$U$15,IF(AND($B5="FRONT",$I3="Oleander"),$C$19:$K$19,IF(AND($B5="BACK",$I3="Oleander"),$M$19:$U$19,IF(AND($B5="FRONT",$I3="IndianMound"),$C$23:$K$23,IF(AND($B5="BACK",$I3="IndianMound"),$M$23:$U$23,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$27:$K$27,IF(AND($B5="BACK",$I3="Sanctuary"),$M$27:$U$27))))))))}
32
33		Comparison	9	13	15	7	3	17	1	11	5		=@INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
34
ParHCPs

Cell Formulas
Range	Formula
C3	C3	=IF(B3="Gina M","F","M")
C7:K7	C7	=IF(B5="FRONT",C11:K11,IF(B5="BACK",M11:U11))
C8:K8	C8	=INDEX(INDIRECT($I$3),1,IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
L8,V25,L25,V21,L21,V17,L17,V13,L13	L8	=SUM(C8:K8)
C9:K9	C9	=INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
W13,W25,W21,W17	W13	=SUM(L13+V13)
C30:K30	C30	=IF(AND(B3=Player,$B5="FRONT",$I3="PineLakes"),$C$14:$K$14,IF(AND($B5="BACK",$I3="PineLakes"),$M$14:$U$14,IF(AND($B5="FRONT",$I3="Oleander"),$C$18:$K$18,IF(AND($B5="BACK",$I3="Oleander"),$M$18:$U$18,IF(AND($B5="FRONT",$I3="IndianMound"),$C$22:$K$22,IF(AND($B5="BACK",$I3="IndianMound"),$M$22:$U$22,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$26:$K$26,IF(AND($B5="BACK",$I3="Sanctuary"),$M$26:$U$26))))))))
C31:K31	C31	=IF(AND(B3="Gina M",$B5="FRONT",$I3="PineLakes"),$C$15:$K$15,IF(AND($B5="BACK",$I3="PineLakes"),$M$15:$U$15,IF(AND($B5="FRONT",$I3="Oleander"),$C$19:$K$19,IF(AND($B5="BACK",$I3="Oleander"),$M$19:$U$19,IF(AND($B5="FRONT",$I3="IndianMound"),$C$23:$K$23,IF(AND($B5="BACK",$I3="IndianMound"),$M$23:$U$23,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$27:$K$27,IF(AND($B5="BACK",$I3="Sanctuary"),$M$27:$U$27))))))))
M30:M31,M33	M30	=FORMULATEXT(C30)
C33:K33	C33	=INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=S22_90!$J$2:$J$19
B5	List	=Side
B13	List	=S22_90!$O$2:$O$6
B17	List	=S22_90!$O$2:$O$6
B21	List	=S22_90!$O$2:$O$6
B25	List	=S22_90!$O$2:$O$6
I3	List	=S22_90!$O$2:$O$6

 

[Fluff]

As this is a totally different question it needs a new thread. Thanks