Correct result for team when home or away

[William53]

Hi All
I have just got back into excel after some years of absence and have a problem which I cannot seem to solve, so ask the experts.

I have the above table showing a team, Wellington Phoenix and the last four actual games with dates. I have a simple formula in R1 column which is just "H" minus "A".
In R2 I have the following formula which refers to R1 column =IF(P2<>"",IF(P2>0,"W",IF(P2<0,"W",IF(P2=0,"D","L")))). This is fine for the actual result of the match but I want R2 to show the actual Wellington Phoenix result. As you can see in the sixth result it is showing a win when in fact Wellington lost.

Is there a formula that can capture what the result is for Wellington whether or not they are home or away?
Any help or assistance would be gratefully received.

Thanks

William

 

[jtakw]

Hi,

May be something like this:

Book3.xlsx
	L	M	N	O	P	Q
1	Home	Away	H	A	R1	R2
2	Wellington Phoenix	Melbourne	1	0	1	W
3	Wellington Phoenix	Adelaide	1	1	0	D
4	Wellington Phoenix	Brisbane	0	0	0	D
5	Wellington Phoenix	Sydney	1	1	0	D
6	Newcastle	Wellington Phoenix	1	2	1	W
7	Wellington Phoenix	Perth	1	2	-1	L
Sheet1025

Cell Formulas
Range	Formula
P2:P7	P2	=IF(COUNT(N2:O2)=2,IF(L2="Wellington Phoenix",N2-O2,O2-N2),"")
Q2:Q7	Q2	=IF(P2="","",IF(P2>0,"W",IF(P2=0,"D","L")))

 

[steve the fish]

Your P2 could be:

=CHOOSE(MATCH("Wellington Phoenix",L2:M2,0),N2-O2,O2-N2)

 

[William53]

Hi "jtakw" and "steve the fish" many thanks for your assistance, both solutions work perfectly well. Made my Monday morning.

 

[jtakw]

You're welcome.