ryansm05

Board Regular
Joined
Sep 14, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top