search/lookup formula help.

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.

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 A23:D23 and subsequent results are
entered in the range A24:D402
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
[email protected]


"Paul T" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>

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

--


XL2003
Regards

William
[email protected]


"William" <[email protected]> wrote in message
news:[email protected]...
> 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 A23:D23 and subsequent results are
> entered in the range A24:D402
> 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
> [email protected]
>
>
> "Paul T" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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, 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/xlFunctio...ml#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
"William" <[email protected]> wrote in message
news:[email protected]...
> Sorry, ignore post - I went the wrong way!!
>
> --
>
>
> XL2003
> Regards
>
> William
> [email protected]
>
>
> "William" <[email protected]> wrote in message
> news:[email protected]...
>> 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 A23:D23 and subsequent results are
>> entered in the range A24:D402
>> 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
>> [email protected]
>>
>>
>> "Paul T" <[email protected]> wrote in message
>> news:[email protected]...
>>>
>>> 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.
>>>
>>
>>
>>
>
>