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

 

[Fluff]

You're welcome & thanks for the feedback.

@mart37
You're formula doesn't work for me, in F29 I just get NEW (H) as neither the row nor the column has 19 twice.

 

[jasonb75]

It was interesting anyway.

Definitely, although I can't help thinking that I still over-complicated my thinking with the original layout of the data in the other thread.

@ryansm05 glad to see you have a solution that works