football formula Fixture table help

[Paul T]

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

 

[Peo Sjoblom]

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

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

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

 

[Paul T]

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