AND and OR with INDEX MATCH problem

markly · Dec 3, 2022

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
Date	NZT	Gp		4	4	4		T1 FT	T1 HT	T2 HT	T2 FT
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

Peter_SSs · Dec 3, 2022

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

markly said:
I cannot figure out why the formula works for teams in Col. F but not for teams in Col. J.

Unfortunately, you did not give us the formula so no chance to analyse it for you.

markly said:
The mini sheet is:

It would be better if you did use 'Mini Sheet' rather than 'Table Only'

markly · Dec 4, 2022

XL2BB did not work as it was supposed to. After selecting my range, I clicked Mini Sheet but got an error message "1004 - Application-defined or object-defined error". I then clicked Table Only and got a message "Mini Sheet was successfully copied to clipboard", so I pasted it in the post.

This is the formula in Q5:
{=IF(AND(OR($F$3:$F$18=P5),ISNUMBER(INDEX($K$3:$K$18,MATCH(P5,$F$3:$F$18,0))),INDEX($K$3:$K$18,MATCH(P5,$F$3:$F$18,0))>INDEX($N$3:$N$18,MATCH(P5,$F$3:$F$18,0))),"W v "&INDEX($J$3:$J$18,MATCH(P5,$F$3:$F$18,0)),
IF(AND(OR($F$3:$F$18=P5),ISNUMBER(INDEX($K$3:$K$18,MATCH(P5,$F$3:$F$18,0))),INDEX($K$3:$K$18,MATCH(P5,$F$3:$F$18,0))=INDEX($N$3:$N$18,MATCH(P5,$F$3:$F$18,0))),"Dr v "&INDEX($J$3:$J$18,MATCH(P5,$F$3:$F$18,0)),
IF(AND(OR($F$3:$F$18=P5),ISNUMBER(INDEX($K$3:$K$18,MATCH(P5,$F$3:$F$18,0))),INDEX($K$3:$K$18,MATCH(P5,$F$3:$F$18,0))<INDEX($N$3:$N$18,MATCH(P5,$F$3:$F$18,0))),"L v "&INDEX($J$3:$J$18,MATCH(P5,$F$3:$F$18,0)),
IF(OR($F$3:$F$18=P5),INDEX($J$3:$J$18,MATCH(P5,$F$3:$F$18,0)),
IF(AND(OR($J$3:$J$18=P5),ISNUMBER(INDEX($N$3:$N$18,MATCH(P5,$J$3:$J$18,0))),INDEX($K$3:$K$18,MATCH(P5,$J$3:$J$18,0))>INDEX($N$3:$N$18,MATCH(P5,$J$3:$J$18,0))),"L v "&INDEX($F$3:$F$18,MATCH(P5,$J$3:$J$18,0)),
IF(AND(OR($J$3:$J$18=P5),ISNUMBER(INDEX($N$3:$N$18,MATCH(P5,$J$3:$J$18,0))),INDEX($K$3:$K$18,MATCH(P5,$J$3:$J$18,0))=INDEX($N$3:$N$18,MATCH(P5,$J$3:$J$18,0))),"Dr v "&INDEX($F$3:$F$18,MATCH(P5,$J$3:$J$18,0)),
IF(AND(OR($J$3:$J$18=P5),ISNUMBER(INDEX($N$3:$N$18,MATCH(P5,$J$3:$J$18,0))),INDEX($K$3:$K$18,MATCH(P5,$J$3:$J$18,0))<INDEX($N$3:$N$18,MATCH(P5,$J$3:$J$18,0))),"W v "&INDEX($F$3:$F$18,MATCH(P5,$J$3:$J$18,0)),
IF(OR($J$3:$J$18=P5),INDEX($F$3:$F$18,MATCH(P5,$J$3:$J$18,0))))))))))}

Peter_SSs · Dec 4, 2022

Thanks for the formula.

What about the first paragraph in my previous post?
Is there some reason why your formula only refers to rows 3:18 when clearly your data is bigger than that?

markly · Dec 4, 2022

Thanks Peter. Have now updated my account details. For some unknown reason, XL2BB just won't let me send you a proper Mini Sheet. Is it because my Excel version is 2010?

Peter_SSs · Dec 4, 2022

markly said:
Have now updated my account details.

Thanks.

markly said:
For some unknown reason, XL2BB just won't let me send you a proper Mini Sheet.

I will try to find out more about that.

markly said:
Is it because my Excel version is 2010?

It shouldn't be as your version is listed as one of the relevant applicable versions

Now, what about my second question in my last post?

(It would make things much easier if I did not have to keep asking things twice

)

smozgur · Dec 4, 2022

markly said:
For some unknown reason, XL2BB just won't let me send you a proper Mini Sheet. Is it because my Excel version is 2010?

@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?

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?

markly · Dec 4, 2022

Sorry about Q2. Col. Q relates to just the first round of the three Group games, which are rows 3:18. There will be a Col. R for rows 19:34 and a Col. S for rows 35:50, the 2nd and third games. There are no named ranges in the sheet, so after the first unsuccessful attempt to create a Mini Sheet I unchecked the Named Ranges box to see if that would help. It didn't.

I tested each IF condition with F9 and got 7 FALSE and one TRUE, as expected, yet the #NA result. At one stage when trying to get the formula to work for a Col. J team, I deleted the first block of the formula (the IF(OR(F3:F18) lines), leaving just the lines starting with IF(OR(J3:J18), and got the correct result. I wonder if the whole formula has too many INDEX/MATCHs for Excel?

How can I send you the spreadsheet independently of XL2BB? The button below only allows an image to be sent.

smozgur · Dec 4, 2022

markly said:
How can I send you the spreadsheet independently of XL2BB? The button below only allows an image to be sent.

@markly: I just started a conversation with you about this. Thanks.

Peter_SSs · Dec 4, 2022

markly said:
How can I send you the spreadsheet independently of XL2BB?

I don't need the actual workbook but @smozgur does - see his two posts above.

Using your original formula structure you could use this massive adaptation

...

Excel Formula:

{=IF(ISERROR(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))))))))))),IF(AND(OR($J$3:$J$18=P4),ISNUMBER(INDEX($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))),INDEX($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))>INDEX($K$3:$K$18,MATCH(P4,$J$3:$J$18,0))),"W 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($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))=INDEX($K$3:$K$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($N$3:$N$18,MATCH(P4,$J$3:$J$18,0))<INDEX($K$3:$K$18,MATCH(P4,$J$3:$J$18,0))),"L 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)),
IF(AND(OR($F$3:$F$18=P4),ISNUMBER(INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))),INDEX($N$3:$N$18,MATCH(P4,$F$3:$F$18,0))>INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))),"L 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($N$3:$N$18,MATCH(P4,$F$3:$F$18,0))=INDEX($K$3:$K$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($N$3:$N$18,MATCH(P4,$F$3:$F$18,0))<INDEX($K$3:$K$18,MATCH(P4,$F$3:$F$18,0))),"W 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($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)))))))))))}

... but I think the considerably shorter one below does the same job (& I don't think that it will need Ctrl+Shift+Enter confirmation in your Excel version)

markly.xlsm

F

J

K

N

O

P

Q

1

Team 1

T1 FT

T2 FT

2

3

Qatar

Ecuador

0

2

Group Stage

Game 1

4

England

Iran

6

2

Argentina

L v Saudi Arabia

5

Senegal

Netherlands

0

2

Australia

L v France

6

USA

Wales

1

Belgium

W v Canada

7

Argentina

Saudi Arabia

1

2

Brazil

Serbia

8

Denmark

Tunisia

0

Cameroon

Switzerland

9

Mexico

Poland

0

Canada

L v Belgium

10

France

Australia

4

1

Costa Rica

L v Spain

11

Morocco

Croatia

0

Croatia

Dr v Morocco

12

Germany

Japan

1

2

Denmark

Dr v Tunisia

13

Spain

Costa Rica

7

0

Ecuador

W v Qatar

14

Belgium

Canada

1

0

England

W v Iran

15

Switzerland

Cameroon

France

W v Australia

16

Uruguay

South Korea

Germany

L v Japan

17

Portugal

Ghana

Portugal

18

Brazil

Serbia

Iran

L v England

19

Wales

Iran

Japan

W v Germany

20

Qatar

Senegal

Mexico

Dr v Poland

21

Netherlands

Ecuador

Morocco

Dr v Croatia

22

England

USA

Netherlands

W v Senegal

23

Tunisia

Australia

Poland

Dr v Mexico

24

Poland

Saudi Arabia

Portugal

Ghana

25

France

Denmark

Qatar

L v Ecuador

26

Argentina

Mexico

Saudi Arabia

W v Argentina

27

Japan

Costa Rica

Senegal

L v Netherlands

28

Belgium

Morocco

Serbia

Brazil

29

Croatia

Canada

South Korea

Uruguay

30

Spain

Germany

Spain

W v Costa Rica

31

Cameroon

Serbia

Switzerland

Cameroon

32

South Korea

Ghana

Tunisia

Dr v Denmark

33

Brazil

Switzerland

Uruguay

South Korea

34

Portugal

Uruguay

USA

Dr v Wales

35

Ecuador

Senegal

Wales

Dr v USA

Sheet1

Cell Formulas
Range	Formula
Q4:Q35	Q4	=IF(ISERROR(IF(VLOOKUP(P4,F$3:K$18,6,0)="","",CHOOSE(SIGN(INDEX(K$3:K$18,MATCH(P4,F$3:F$18,0))-INDEX(N$3:N$18,MATCH(P4,F$3:F$18,0)))+2,"L v ","Dr v ","W v "))),IF(VLOOKUP(P4,J$3:N$18,5,0)="","",CHOOSE(SIGN(INDEX(N$3:N$18,MATCH(P4,J$3:J$18,0))-INDEX(K$3:K$18,MATCH(P4,J$3:J$18,0)))+2,"L v ","Dr v ","W v ")),IF(VLOOKUP(P4,F$3:K$18,6,0)="","",CHOOSE(SIGN(INDEX(K$3:K$18,MATCH(P4,F$3:F$18,0))-INDEX(N$3:N$18,MATCH(P4,F$3:F$18,0)))+2,"L v ","Dr v ","W v ")))&IF(COUNTIF(F$3:F$18,P4),INDEX(J$3:J$18,MATCH(P4,F$3:F$18,0)),INDEX(F$3:F$18,MATCH(P4,J$3:J$18,0)))

AND and OR with INDEX MATCH problem

markly

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

markly

New Member

Peter_SSs

MrExcel MVP, Moderator

markly

New Member

Peter_SSs

MrExcel MVP, Moderator

smozgur

BatCoder

markly

New Member

smozgur

BatCoder

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

AND and OR with INDEX MATCH problem

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

BatCoder

New Member

BatCoder

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock