Hi, I hope yo can help
I've a grid with home team on the y axis and away team on the x. I've each fixture where the teams meet within the grid (so Arsenal v Leicester = 1, Leicester v Arsenal = 31 e.g.)
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 482"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Arsenal[/TD]
[TD]Bournemouth[/TD]
[TD]Brighton[/TD]
[TD]Burnley[/TD]
[TD]Chelsea[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]Bournemouth[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Brighton[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Burnley[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I'd like a formula which, if I input "Arsenal" in cell b1 and "1" in a2, it will return "Leicester" in b2.
**
I have tried combining an if function with V & H Lookups, or index or match functions, but to no avail.
Obviously each team plays 38 fixtures, and meets each opponent twice.
Essentially I want to replace the Away teams on the x axis with the fixture number, and subsequently the fixture numbers within the grid, with the opponents to make later referencing easier.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 482"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Home\/ Away>[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD] Leicester[/TD]
[TD="align: right"]Stoke[/TD]
[TD="align: right"]Liverpool[/TD]
[TD="align: right"]Bournemouth[/TD]
[TD="align: right"]Chelsea[/TD]
[/TR]
[TR]
[TD]Bournemouth[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Brighton[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Burnley[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Any help is appreciated
**( I mean, eventually, I'm aiming to input "1" into a cell and have each of the fixtures displayed across a range, but I reckon I can sort that afterwards)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Fixture[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD][/TD]
[TD][/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD][/TD]
[TD][/TD]
[TD]Leicester[/TD]
[/TR]
[TR]
[TD]Brighton[/TD]
[TD][/TD]
[TD][/TD]
[TD]Man Utd[/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD][/TD]
[TD][/TD]
[TD]Burnley[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've a grid with home team on the y axis and away team on the x. I've each fixture where the teams meet within the grid (so Arsenal v Leicester = 1, Leicester v Arsenal = 31 e.g.)
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 482"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Arsenal[/TD]
[TD]Bournemouth[/TD]
[TD]Brighton[/TD]
[TD]Burnley[/TD]
[TD]Chelsea[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]Bournemouth[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Brighton[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Burnley[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I'd like a formula which, if I input "Arsenal" in cell b1 and "1" in a2, it will return "Leicester" in b2.
**
I have tried combining an if function with V & H Lookups, or index or match functions, but to no avail.
Obviously each team plays 38 fixtures, and meets each opponent twice.
Essentially I want to replace the Away teams on the x axis with the fixture number, and subsequently the fixture numbers within the grid, with the opponents to make later referencing easier.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 482"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Home\/ Away>[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD] Leicester[/TD]
[TD="align: right"]Stoke[/TD]
[TD="align: right"]Liverpool[/TD]
[TD="align: right"]Bournemouth[/TD]
[TD="align: right"]Chelsea[/TD]
[/TR]
[TR]
[TD]Bournemouth[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Brighton[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Burnley[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Any help is appreciated
**( I mean, eventually, I'm aiming to input "1" into a cell and have each of the fixtures displayed across a range, but I reckon I can sort that afterwards)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Fixture[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD][/TD]
[TD][/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD][/TD]
[TD][/TD]
[TD]Leicester[/TD]
[/TR]
[TR]
[TD]Brighton[/TD]
[TD][/TD]
[TD][/TD]
[TD]Man Utd[/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD][/TD]
[TD][/TD]
[TD]Burnley[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]