football formula Fixture table help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul T - 04 Sep 2005 10:54 GMT

Hi all

Looking for a formula to locate a score from a results table place it next to the fixture list.
(backward sounding I know but from fixture list I can ascertain current form, but I cant from a table)
Created a fixture list I.e. Arsenal vs. Aston Villa is the first fixture. and I want to pull the result for each team from the table of results.

Results Table Sheet

a bcd e f g h i j
1 ________Arse| A Vil |Che |
2 Arsenal |XXXX| 0 -1 | |
3 A Villa | |XXXXX| 1-0 |
4 Chelsea | | |XXXX|

Fixture Sheet

a b c d
1 Arsenal - A Villa 0 - 1

So basically find arsenal at home and villa away from fixture sheet and match with arsenal home villa away on the result table, display 0 for arsenal and 1 for villa.

I tried index&match, but had no luck - vlookup&hlookup can only search 1 variable.

Any help gratefully appreciated.

Paul T

Reply to this Message

Peo Sjoblom - 04 Sep 2005 17:41 GMT

You can use index(range, match1, match2) like in this link

http://www.contextures.com/xlFunctions03.html#IndexMatch2

Signature

Regards,

Peo Sjoblom

(No private emails please)

Hi all

Looking for a formula to locate a score from a results table place it next
to the fixture list.
(backward sounding I know but from fixture list I can ascertain current
form, but I cant from a table)
Created a fixture list I.e. Arsenal vs. Aston Villa is the first fixture.
and I want to pull the result for each team from the table of results.

Results Table Sheet

a bcd e f g h i j
1 ________Arse| A Vil |Che |
2 Arsenal |XXXX| 0 -1 | |
3 A Villa | |XXXXX| 1-0 |
4 Chelsea | | |XXXX|

Fixture Sheet

a b c d
1 Arsenal - A Villa 0 - 1

So basically find arsenal at home and villa away from fixture sheet and
match with arsenal home villa away on the result table, display 0 for
arsenal and 1 for villa.

I tried index&match, but had no luck - vlookup&hlookup can only search 1
variable.

Any help gratefully appreciated.

Paul T

Reply to this Message

Paul T - 04 Sep 2005 19:43 GMT

Peo - Your a star!

Home team Formula
=INDEX(Table,MATCH(CF129,INDEX(Table,,1),0),MATCH(CG129,INDEX(Table,1,),0))

Away Team Formula
=INDEX(Table,MATCH(CF129,INDEX(Table,,1),0),MATCH(CG129,INDEX(Table,1,),1))

Took a little while to suss the away result but got there in the end.

Many thanks again Peo

Paul T

> You can use index(range, match1, match2) like in this link
>
[quoted text clipped - 33 lines]
>
> Paul T

Reply to this Message