search/lookup formula 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 _______Ars| Vil |Che |
2 Arsenal |xxx| 0 -1| |
3 A Villa | |x x x| 1-0 |
4 Chelsea | | |x x x|


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
PS:If this message has duplicated on the message board - apologises.

 

[William]

Hi Paul

In range A2:A21 enter the team names
In range B1:U1 enter the team names
The first result is shown in cells A2323 and subsequent results are
entered in the range A24402
Enter the formula below in cell B2 and then copy down to B21.
Copy the range B2:B21 to cells C2:U21

This is all one line if text wraps.....
=IF($A2=B$1,"xxx",SUMPRODUCT(($A$23:$A$402=$A2)*($B$23:$B$402=B$1)*$C$23:$C$402)&"-"&SUMPRODUCT(($A$23:$A$402=$A2)*($B$23:$B$402=B$1)*$D$23:$D$402))


--


XL2003
Regards

William
(e-mail address removed)

 

[William]

Sorry, ignore post - I went the wrong way!!

--


XL2003
Regards

William
(e-mail address removed)

 

[Paul T]

William, thanks for taking time to have a look and offer help, but now
sussed thanks to someone called PEO who pointed my in the right
direction.http://www.contextures.com/xlFunctions03.html#IndexMatch2 it gave
a couple of examples.

My Final Formula
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.

Thanks again.
Paul T