AND and OR with INDEX MATCH problem

[markly]

I have a spreadsheet for the World Cup, with the each game's teams in Col. F and Col. J and their scores in Cols K-N. In Col. P all teams are listed in alphabetical order. Alongside that column I want to show just the opposing team if the game has not yet played, or if it has played "W v", "Dr v" or "L v" and the opposing team.

My formula works perfectly if the alphabetical team is in Col. F, but I get #NA if the alphabetical team is in Col. J. I cannot figure out why the formula works for teams in Col. F but not for teams in Col. J. The mini sheet is:

Date	NZT	Gp	Team 1		Draw	Team 2		T1 FT	T1 HT	T2 HT	T2 FT
				4	4	4
Mon 21 Nov		A	Qatar	6.00	5.00	2.00	Ecuador	0	0	2	2		Group Stage	Game 1
Tue 22 Nov	2am	B	England	1.30	4.50	11.00	Iran	6	3	0	2		Argentina	L v Saudi Arabia
Tue 22 Nov	5am	A	Senegal	6.00	3.60	1.62	Netherlands	0	0	0	2		Australia	#N/A
Tue 22 Nov	8am	B	USA	2.37	3.00	3.30	Wales	1	1	0	1		Belgium	W v Canada
Tue 22 Nov	11pm	C	Argentina	1.12	8.00	19.00	Saudi Arabia	1	1	0	2		Brazil	Serbia
Wed 23 Nov	2am	D	Denmark	1.53	3.75	6.50	Tunisia	0	0	0	0		Cameroon	#N/A
Wed 23 Nov	5am	C	Mexico	2.50	2.88	3.00	Poland	0	0	0	0		Canada	#N/A
Wed 23 Nov	8am	D	France	1.22	6.00	13.00	Australia	4	2	1	1		Costa Rica	#N/A
Wed 23 Nov	11pm	F	Morocco	3.75	3.10	2.00	Croatia	0	0	0	0		Croatia	#N/A
Thu 24 Nov	2am	E	Germany	1.40	4.50	7.00	Japan	1	1	0	2		Denmark	Dr v Tunisia
Thu 24 Nov	5am	E	Spain	1.12	8.00	20.00	Costa Rica	7	3	0	0		Ecuador	#N/A
Thu 24 Nov	8am	F	Belgium	1.20	4.20	6.50	Canada	1	1	0	0		England	W v Iran
Thu 24 Nov	11pm	G	Switzerland	1.72	3.40	5.00	Cameroon						France	W v Australia
Fri 25 Nov	2am	H	Uruguay	1.67	3.50	5.50	South Korea						Germany	L v Japan
Fri 25 Nov	5am	H	Portugal	1.36	4.50	9.00	Ghana						Ghana	#N/A
Fri 25 Nov	8am	G	Brazil	1.45	4.33	7.00	Serbia						Iran	#N/A
Fri 25 Nov	11pm	B	Wales	2.00	3.00	4.00	Iran						Japan	#N/A
Sat 26 Nov	2am	A	Qatar	6.00	3.60	1.62	Senegal						Mexico	Dr v Poland
Sat 26 Nov	5am	A	Netherlands	1.72	3.50	4.50	Ecuador						Morocco	Dr v Croatia
Sat 26 Nov	8am	B	England	1.53	4.00	5.50	USA						Netherlands	#N/A
Sat 26 Nov	11pm	D	Tunisia	2.10	3.10	3.60	Australia						Poland	#N/A
Sun 27 Nov	2am	C	Poland	1.83	3.40	4.50	Saudi Arabia						Portugal	Ghana
Sun 27 Nov	5am	D	France	1.80	3.50	4.50	Denmark						Qatar	L v Ecuador
Sun 27 Nov	8am	C	Argentina	1.50	4.00	7.00	Mexico						Saudi Arabia	#N/A
Sun 27 Nov	11pm	E	Japan	1.45	4.20	7.50	Costa Rica						Senegal	L v Netherlands
Mon 28 Nov	2am	F	Belgium	1.83	3.40	4.50	Morocco						Serbia	#N/A
Mon 28 Nov	5am	F	Croatia	2.25	3.25	3.30	Canada						South Korea	#N/A
Mon 28 Nov	8am	E	Spain	2.30	3.50	2.88	Germany						Spain	W v Costa Rica
Mon 28 Nov	11pm	G	Cameroon	5.00	3.60	1.72	Serbia						Switzerland	Cameroon
Tue 29 Nov	2am	H	South Korea	2.60	3.00	2.88	Ghana						Tunisia	#N/A
Tue 29 Nov	5am	G	Brazil	1.40	4.33	7.50	Switzerland						Uruguay	South Korea
Tue 29 Nov	8am	H	Portugal	1.91	3.25	4.20	Uruguay						USA	Dr v Wales
Wed 30 Nov	4am	A	Ecuador				Senegal						Wales	#N/A

 

[markly]

Thanks so much, Peter. The short formula does the job perfectly, so I did not try your long adaptation of mine. You are right about not needing to use Ctrl+Shift+Enter with the short formula. Your ability with Excel is truly amazing (at least to me) to come up with such an elegant formula involving IF, ISERROR, VLOOKUP, CHOOSE, SIGN, INDEX, MATCH and COUNTIF. [I will now try to reply to smozgur re XL2BB.]

 

[Peter_SSs]

Thanks so much, Peter. The short formula does the job perfectly, ..

You're welcome. Thanks for the confirmation.

... so I did not try your long adaptation of mine.

I don't blame you!

I will now try to reply to smozgur re XL2BB.

Great.

 

[markly]

@markly: It could be something related to the named range(s) in the workbook. Could you please retry by unchecking the Named Ranges option in the Additional Info section?

View attachment 80153

If it works when you unchecked the option then it means XL2BB can't work with the named range(s) in your workbook. I need to see the workbook to be able to give more information in this case.

If it doesn't work (or there is no named range in the workbook), then I can try to find out if it is related to Excel version or actually what it is, but I'll still need the workbook. Perhaps you could upload it somewhere that I can download and test?

Hi smozgur
You have kindly offered to try to solve the XL2BB problem that prevented me creating a Mini Sheet. For that you need my workbook. How can I send it to you when I can upload only images via the forum? Do you have an email address that I can send the workbook to, or is there some other way?

 

[Peter_SSs]

Do you have an email address that I can send the workbook to, or is there some other way?

Check your Conversations at the top right of the forum.

 

[smozgur]

Thanks for the sample workbook, @markly.

I tested it on Excel 2010/Windows 7, and I can get the mini-sheet without problems. I selected the whole range (except below row 52 because it exceeds the limit that could be posted on the forum with XL2BB), and it generated the mini-sheet code as shown below.

Does XL2BB still fail if you open a brand new workbook, enter something simple into a small range and create a mini-sheet by selecting that range? I believe it should because I don't think this is related to the workbook. It could be something related to your Excel installation, or even Windows, I really can't tell what it is. I'm afraid I am stuck at this point since I can't reproduce the same behavior by using the exact same workbook.

2022 World Cup 0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Stage		Date	NZT	Gp	Team 1		Draw	Team 2		T1 FT	T1 HT	T2 HT	T2 FT
2							4	4	4
3	Group	1st Games	Mon 21 Nov		A	Qatar	6.00	5.00	2.00	Ecuador	0	0	2	2		Group Stage	Game 1
4			Tue 22 Nov	2am	B	England	1.30	4.50	11.00	Iran	6	3	0	2		Argentina	L v Saudi Arabia
5			Tue 22 Nov	5am	A	Senegal	6.00	3.60	1.62	Netherlands	0	0	0	2		Australia	#N/A
6			Tue 22 Nov	8am	B	USA	2.37	3.00	3.30	Wales	1	1	0	1		Belgium	W v Canada
7			Tue 22 Nov	11pm	C	Argentina	1.12	8.00	19.00	Saudi Arabia	1	1	0	2		Brazil	Serbia
8			Wed 23 Nov	2am	D	Denmark	1.53	3.75	6.50	Tunisia	0	0	0	0		Cameroon	#N/A
9			Wed 23 Nov	5am	C	Mexico	2.50	2.88	3.00	Poland	0	0	0	0		Canada	#N/A
10			Wed 23 Nov	8am	D	France	1.22	6.00	13.00	Australia	4	2	1	1		Costa Rica	#N/A
11			Wed 23 Nov	11pm	F	Morocco	3.75	3.10	2.00	Croatia	0	0	0	0		Croatia	#N/A
12			Thu 24 Nov	2am	E	Germany	1.40	4.50	7.00	Japan	1	1	0	2		Denmark	Dr v Tunisia
13			Thu 24 Nov	5am	E	Spain	1.12	8.00	20.00	Costa Rica	7	3	0	0		Ecuador	#N/A
14			Thu 24 Nov	8am	F	Belgium	1.20	4.20	6.50	Canada	1	1	0	0		England	W v Iran
15			Thu 24 Nov	11pm	G	Switzerland	1.72	3.40	5.00	Cameroon						France	W v Australia
16			Fri 25 Nov	2am	H	Uruguay	1.67	3.50	5.50	South Korea						Germany	L v Japan
17			Fri 25 Nov	5am	H	Portugal	1.36	4.50	9.00	Ghana						Ghana	#N/A
18			Fri 25 Nov	8am	G	Brazil	1.45	4.33	7.00	Serbia						Iran	#N/A
19		2nd Games	Fri 25 Nov	11pm	B	Wales	2.00	3.00	4.00	Iran						Japan	#N/A
20			Sat 26 Nov	2am	A	Qatar	6.00	3.60	1.62	Senegal						Mexico	Dr v Poland
21			Sat 26 Nov	5am	A	Netherlands	1.72	3.50	4.50	Ecuador						Morocco	Dr v Croatia
22			Sat 26 Nov	8am	B	England	1.53	4.00	5.50	USA						Netherlands	#N/A
23			Sat 26 Nov	11pm	D	Tunisia	2.10	3.10	3.60	Australia						Poland	#N/A
24			Sun 27 Nov	2am	C	Poland	1.83	3.40	4.50	Saudi Arabia						Portugal	Ghana
25			Sun 27 Nov	5am	D	France	1.80	3.50	4.50	Denmark						Qatar	L v Ecuador
26			Sun 27 Nov	8am	C	Argentina	1.50	4.00	7.00	Mexico						Saudi Arabia	#N/A
27			Sun 27 Nov	11pm	E	Japan	1.45	4.20	7.50	Costa Rica						Senegal	L v Netherlands
28			Mon 28 Nov	2am	F	Belgium	1.83	3.40	4.50	Morocco						Serbia	#N/A
29			Mon 28 Nov	5am	F	Croatia	2.25	3.25	3.30	Canada						South Korea	#N/A
30			Mon 28 Nov	8am	E	Spain	2.30	3.50	2.88	Germany						Spain	W v Costa Rica
31			Mon 28 Nov	11pm	G	Cameroon	5.00	3.60	1.72	Serbia						Switzerland	Cameroon
32			Tue 29 Nov	2am	H	South Korea	2.60	3.00	2.88	Ghana						Tunisia	#N/A
33			Tue 29 Nov	5am	G	Brazil	1.40	4.33	7.50	Switzerland						Uruguay	South Korea
34			Tue 29 Nov	8am	H	Portugal	1.91	3.25	4.20	Uruguay						USA	Dr v Wales
35		3rd Games	Wed 30 Nov	4am	A	Ecuador				Senegal						Wales	#N/A
36			Wed 30 Nov	4am	A	Netherlands				Qatar
37			Wed 30 Nov	8am	B	Iran				USA
38			Wed 30 Nov	8am	B	Wales				England
39			Thu 1 Dec	4am	D	Tunisia				France
40			Thu 1 Dec	4am	D	Australia				Denmark
41			Thu 1 Dec	8am	C	Poland				Argentina
42			Thu 1 Dec	8am	C	Saudi Arabia				Mexico
43			Fri 2 Dec	4am	F	Croatia				Belgium
44			Fri 2 Dec	4am	F	Canada				Morocco
45			Fri 2 Dec	8am	E	Japan				Spain
46			Fri 2 Dec	8am	E	Costa Rica				Germany
47			Sat 3 Dec	4am	H	South Korea				Portugal
48			Sat 3 Dec	4am	H	Ghana				Uruguay
49			Sat 3 Dec	8am	G	Serbia				Switzerland
50			Sat 3 Dec	8am	G	Cameroon				Brazil
Games

Cell Formulas
Range	Formula
G2	G2	=SUM(IF(K3:K50>N3:N50,1))
H2	H2	=SUM(IF((ISNUMBER(N3:N50))*(K3:K50=N3:N50),1))
I2	I2	=SUM(IF(K3:K50<N3:N50,1))
Q4:Q35	Q4	=IF(AND(OR($F$3:$F$18=P4),ISNUMBER(INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))),INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))>INDEX($N$3:$N$18,MATCH(P4,$F$3:$F$18,0))),"W v "&INDEX($J$3:$J$18,MATCH(P4,$F$3:$F$18,0)), IF(AND(OR($F$3:$F$18=P4),ISNUMBER(INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))),INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))=INDEX($N$3:$N$18,MATCH(P4,$F$3:$F$18,0))),"Dr v "&INDEX($J$3:$J$18,MATCH(P4,$F$3:$F$18,0)), IF(AND(OR($F$3:$F$18=P4),ISNUMBER(INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))),INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))<INDEX($N$3:$N$18,MATCH(P4,$F$3:$F$18,0))),"L v "&INDEX($J$3:$J$18,MATCH(P4,$F$3:$F$18,0)), IF(OR($F$3:$F$18=P4),INDEX($J$3:$J$18,MATCH(P4,$F$3:$F$18,0)), IF(AND(OR($J$3:$J$18=P4),ISNUMBER(INDEX($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))),INDEX($K$3:$K$18,MATCH(P4,$J$3:$J$18,0))>INDEX($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))),"L v "&INDEX($F$3:$F$18,MATCH(P4,$J$3:$J$18,0)), IF(AND(OR($J$3:$J$18=P4),ISNUMBER(INDEX($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))),INDEX($K$3:$K$18,MATCH(P4,$J$3:$J$18,0))=INDEX($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))),"Dr v "&INDEX($F$3:$F$18,MATCH(P4,$J$3:$J$18,0)), IF(AND(OR($J$3:$J$18=P4),ISNUMBER(INDEX($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))),INDEX($K$3:$K$18,MATCH(P4,$J$3:$J$18,0))<INDEX($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))),"W v "&INDEX($F$3:$F$18,MATCH(P4,$J$3:$J$18,0)), IF(OR($J$3:$J$18=P4),INDEX($F$3:$F$18,MATCH(P4,$J$3:$J$18,0))))))))))
C4,C47,C43,C39,C35,C32,C28,C24,C20,C16,C12,C8	C4	=C3+1
C48:C50,C44:C46,C40:C42,C36:C38,C33:C34,C29:C31,C25:C27,C21:C23,C17:C19,C13:C15,C9:C11,C5:C7	C5	=C4
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:I50	Expression	=K3<N3	text	NO
H3:H50	Expression	=AND(ISNUMBER(N3),K3=N3)	text	NO
G3:G50	Expression	=K3>N3	text	NO

 

[markly]

I have just created this Mini Sheet okay:

Book1
	A	B	C
1	A	B	C
2	Green	Blue	Red
3	Pink	Yellow	Orange
4
5
6
Sheet1

so immediately tried again with C1:Q35 of the World Cup workbook but again got the error message (see image).
No point in spending any more time on the problem, so thanks very much for looking into it.
Kind regards
Mark