Index Match with duplicates

[ryansm05]

Hi,

I have a pivot table which lists football teams down column A (home team) and across row 2 (away team).

The values in the pivot table are the game week that the fixtures will be played. From this, I've been able to create an Index Match which summaries the fixtures almost as required. The problem is that in some game weeks, a team may have two fixtures (which is where my limitations of Excel comes in). The below formula has 2 x index match and will stop searching for additional matches once a match has been returned.

I have converted my Excel to a Google Sheet and pasted the link below. The formulas are within C25:I25 and an example of what I'm trying to achieve in F26:F27 can be found in K26:K27.

If anyone has any suggestions I'd be super grateful.

Thanks
Ryan

=IFERROR(IFERROR(INDEX($B$2:$U$2,MATCH(C$24,$B3:$U3,0))&" (H)",INDEX($A$3:$A$22,MATCH(C$24,$B$3:$B$22,0))&" (A)"),"")

Fixtures Index/Match fix

Sheet1 Min of Gameweek,Column Labels ARS,AVL,BHA,BUR,CHE,CRY,EVE,FUL,LEE,LEI,LIV,MCI,MUN,NEW,SHU,SOU,TOT,WBA,WHU ARS AVL BHA BUR CHE CRY EVE FUL LEE LEI LIV MCI MUN NEW SHU SOU TOT WBA WHU WOL 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18 ARS,vs,BRE(A),CHE(H),MCI(A),NOR(H),BUR(A),TOT(H),BHA(A),CR...

docs.google.com

 

[jasonb75]

There are several ways to solve this, so a couple of things before I post an answer as the best method will vary depending on these requirements.

Do you need this to work with google sheets as well, or have you only used that to post the example?

Could you update your profile to show the version of excel that you use (only the oldest one if you use multiple versions), to do this, click your user name at the top right of the page, then go to Account Details, choose a version of excel from those available then scroll to the bottom and save.

Also, (preferable but not essential) there is a link in my signature block to the XL2BB add in which allows you to post small examples straight to the forum.

 

[Fluff]

I suggest that you update your Account details (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’)

Maybe in C25 copied across

=TRIM(TEXTJOIN(" (H) ",1,FILTER($B$2:$U$2,$B3:$U3=C$24,"")," ")&TEXTJOIN(" (A) ",1,FILTER($A$3:$A$22,$B$3:$B$22=C$24,"")," "))

 

[Fluff]

Another to filter for the team as well

Fixtures Index_Match fix.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Min of Gameweek	Column Labels
2	Row Labels	ARS	AVL	BHA	BUR	CHE	CRY	EVE	FUL	LEE	LEI	LIV	MCI	MUN	NEW	SHU	SOU	TOT	WBA	WHU	WOL
3	ARS		8	38	12	15	18	33	32	24	6	30	25	21	19	4	13	28	35	2	10
4	AVL	23		9	13	38	15	0	30	6	25	4	32	35	19	2	7	0	33	22	27
5	BHA	16	24		8	1	25	31	20	34	27	10	37	3	29	14	11	21	6	36	17
6	BUR	27	20	23		7	9	11	0	36	29	37	22	18	31	16	3	6	25	34	14
7	CHE	36	16	32	21		4	27	34	11	37	2	17	26	24	8	5	10	30	14	20
8	CRY	37	36	5	24	31		3	26	8	16	14	34	29	10	17	1	12	28	20	21
9	EVE	14	34	4	28	12	30		24	10	20	5	0	8	21	36	26	32	2	17	37
10	FUL	1	3	13	35	19	6	9		29	22	12	28	19	38	25	15	0	7	23	31
11	LEE	9	26	19	15	28	23	22	2		7	32	4	33	13	30	0	35	38	12	5
12	LEI	26	5	12	2	19	33	13	10	21		24	30	15	35	28	19	38	32	4	8
13	LIV	3	31	22	19	29	38	25	27	1	9		23	19	33	6	35	13	15	7	11
14	MCI	5	19	18	10	35	19	38	11	31	3	8		27	15	21	0	24	13	26	29
15	MUN	7	17	30	32	6	2	23	37	14	36	34	12		25	20	22	4	9	28	16
16	NEW	34	28	2	4	9	22	7	14	20	17	16	36	5		37	23	30	12	32	26
17	SHU	31	29	33	38	23	35	15	5	3	11	26	7	13	18		27	19	22	9	1
18	SOU	20	21	28	30	25	32	6	36	37	34	17	14	10	8	12		2	4	16	24
19	TOT	11	37	7	26	22	27	1	18	17	14	20	9	31	3	34	29		23	5	36
20	WBA	17	14	26	5	3	11	29	21	16	1	36	20	24	27	10	31	8		37	34
21	WHU	29	10	15	19	33	13	35	8	27	31	21	6	11	1	24	38	25	19		3
22	WOL	22	12	35	33	13	7	18	4	25	23	28	2	38	6	32	9	15	19	30
23
24			16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34
25	ARS	vs	BHA (A)	WBA (A)	CRY (H)	NEW (H)	SOU (A)	MUN (H)	WOL (A)	AVL (A)	LEE (H)	MCI (H)	LEI (A)	BUR (A)	TOT (H)	WHU (A)	LIV (H)	SHU (A)	FUL (H)	EVE (H)	NEW (A)
26	BUR	vs	SHU (H)		MUN (H)	LIV (A) WHU (A)	AVL (H)	CHE (A)	MCI (H)	BHA (H)	CRY (A)	WBA (H)	TOT (A)	ARS (H)	EVE (A)	LEI (H)	SOU (A)	NEW (H)	MUN (A)	WOL (A)	WHU (H)
27	LIV	vs	NEW (A)	SOU (A)		BUR (H) MUN (H)	TOT (A)	WHU (A)	BHA (H)	MCI (H)	LEI (A)	EVE (H)	SHU (A)	FUL (H)	WOL (A)	CHE (H)	ARS (A)	AVL (H)	LEE (A)	NEW (H)	MUN (A)
Sheet1

Cell Formulas
Range	Formula
C25:U27	C25	=TRIM(TEXTJOIN(" (H) ",1,FILTER($B$2:$U$2,FILTER($B$3:$U$22,$A$3:$A$22=$A25)=C$24,"")," ")&TEXTJOIN(" (A) ",1,FILTER($A$3:$A$22,FILTER($B$3:$U$22,$B$2:$U$2=$A25)=C$24,"")," "))

 

[jasonb75]

I don't think that the OP has the dynamic array functions, Fluff.
I didn't recognise the user name when I submitted my reply above, but I've already suggested a formula using them in an earlier thread which they were unable to use.

 

[Fluff]

We'll just have to wait & see. It was interesting anyway.

 

[ryansm05]

I don't think that the OP has the dynamic array functions, Fluff.
I didn't recognise the user name when I submitted my reply above, but I've already suggested a formula using them in an earlier thread which they were unable to use.

Hi Fluff, Jason,

I've updated my details and I do in fact use Office 365 (apol Jason - must have been doing something wrong on the previous thread). Therefore, within Excel, Fluff's* formula works absolutely perfectly. However, as you anticipated, it is not compatible with Google Sheets (GS).

If I did want to replicate this in GS where the dynamic array is not possible - is there a workaround?

Cheers
Ryan

 

[Fluff]

I don't think the filter function in sheets has internal error handling, so it would need to be something like

=TRIM(TEXTJOIN(" (H) ",1,ifna(filter($B$2:$U$2,FILTER($B$3:$U$22,$A$3:$A$22=$A25)=C$24),"")," ")&TEXTJOIN(" (A) ",1,ifna(FILTER($A$3:$A$22,FILTER($B$3:$U$22,$B$2:$U$2=$A25)=C$24),"")," "))

 

[mart37]

Fixtures Index_Match fix.xlsx
	A	B	C	D	E	F	G	H	I
28	ARS		BHA (A)	WBA (A)	CRY (H)	NEW (H)	SOU (A)	MUN (H)	WOL (A)
29	AVL		CHE (A)	MUN (A)		MCI (H) NEW (H)	BUR (A)	SOU (A)	WHU (H)
30	LIV		NEW (A)	SOU (A)		BUR (H) MUN (H)	TOT (A)	WHU (A)	BHA (H)
Sheet1

Cell Formulas
Range	Formula
C28:I28	C28	=IFERROR(IFERROR(INDEX($B$2:$U$2,MATCH(C$24,$B3:$U3,0))&" (H)",INDEX($A$3:$A$22,MATCH(C$24,$B$3:$B$22,0))&" (A)"),"")&IF(COUNTIF($B3:$U3,C$24)=2," "&INDEX($B$2:$U$2,LARGE(INDEX((COLUMN($B3:$U3)-1)*($B3:$U3=C$24),0),1))&" (H)","")&IF(COUNTIF($B$3:$B$22,C$24)=2," "&INDEX($A$3:$A$22,LARGE(INDEX((ROW($A$3:$A$22)-2)*($B$3:$B$22=C$24),0),1))&" (A)","")
C29:I29	C29	=IFERROR(IFERROR(INDEX($B$2:$U$2,MATCH(C$24,$B4:$U4,0))&" (H)",INDEX($A$3:$A$22,MATCH(C$24,$C$3:$C$22,0))&" (A)"),"")&IF(COUNTIF($B4:$U4,C$24)=2," "&INDEX($B$2:$U$2,LARGE(INDEX((COLUMN($B4:$U4)-1)*($B4:$U4=C$24),0),1))&" (H)","")&IF(COUNTIF($C$3:$C$22,C$24)=2," "&INDEX($A$3:$A$22,LARGE(INDEX((ROW($A$3:$A$22)-2)*($C$3:$C$22=C$24),0),1))&" (A)","")
C30:I30	C30	=IFERROR(IFERROR(INDEX($B$2:$U$2,MATCH(C$24,$B13:$U13,0))&" (H)",INDEX($A$3:$A$22,MATCH(C$24,$L$3:$L$22,0))&" (A)"),"")&IF(COUNTIF($B13:$U13,C$24)=2," "&INDEX($B$2:$U$2,LARGE(INDEX((COLUMN($B13:$U13)-1)*($B13:$U13=C$24),0),1))&" (H)","")&IF(COUNTIF($L$3:$L$22,C$24)=2," "&INDEX($A$3:$A$22,LARGE(INDEX((ROW($A$3:$A$22)-2)*($L$3:$L$22=C$24),0),1))&" (A)","")

 

[ryansm05]

I don't think the filter function in sheets has internal error handling, so it would need to be something like

=TRIM(TEXTJOIN(" (H) ",1,ifna(filter($B$2:$U$2,FILTER($B$3:$U$22,$A$3:$A$22=$A25)=C$24),"")," ")&TEXTJOIN(" (A) ",1,ifna(FILTER($A$3:$A$22,FILTER($B$3:$U$22,$B$2:$U$2=$A25)=C$24),"")," "))

That's incredible. It now also works in GS. Thank you much Fluff!

Have an awesome weekend

Cheers
Ryan