Index Match with duplicates

ryansm05

Board Regular
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

Excel Formula:
``=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)"),"")``

Fluff

MrExcel MVP, Moderator
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.

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jasonb75

Well-known Member
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

Replies
0
Views
147
Replies
1
Views
189
Replies
4
Views
329
Replies
3
Views
306
Replies
3
Views
319

1,129,452
Messages
5,636,340
Members
416,914
Latest member
DWC199

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back