Generate a table of Win, Loss, Tie results from a separate table of numerical results.

[HockeyBoi]

So I had some great help from a user the other day, and I'm hoping to take it a step further.

I'd like to be able to input the results of sports matches into the table on the left, and have the table on the right generate a "W", "L", or "T" for that particular team. I've written a bit of code that will easily do this, but I can't apply it across the table without doing it manually for each individual cell and each individual team.

Here's what I've written:
=IF(OR(ISBLANK(C3), ISBLANK(D3)), "", IF(C3 > D3, "W", IF(C3 < D3, "L", "T")))

Is there a better/easier way to apply this across the matches?

Example.xlsx
	B	C	D	E	F	G	H	I	J	K
2							Game 1	Game 2	Game 3	Game 4
3	Team 1			Team 2		Team 1
4	Team 3			Team 4		Team 2
5	Team 5			Team 6		Team 3
6	Team 7			Team 8		Team 4
7	Team 9			Team 10		Team 5
8	Team 11			Team 12		Team 6
9	Team 2			Team 1		Team 7
10	Team 13			Team 8		Team 8
11	Team 10			Team 7		Team 9
12	Team 14			Team 4		Team 10
13	Team 6			Team 3		Team 11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:E13	Cell Value	contains "Jets"	text	NO
B3:E13	Cell Value	contains "Bucks"	text	NO
B3:E13	Cell Value	contains "Lignu"	text	NO
B3:E13	Cell Value	contains "Bulls"	text	NO
B3:E13	Cell Value	contains "OPS Beasts 2.0"	text	NO
B3:E13	Cell Value	contains "Emersons Steamers"	text	NO
B3:E13	Cell Value	contains "Pro Flow Falcons"	text	NO
B3:E13	Cell Value	contains "Bombers"	text	NO
B3:E13	Cell Value	contains "Kinoko Beavers"	text	NO
B3:E13	Cell Value	contains "Chargers"	text	NO
B3:E13	Cell Value	contains "Darkest Dunedin Ducks"	text	NO
B3:E13	Cell Value	contains "Aotea Amps"	text	NO
B3:E13	Cell Value	contains "Rams"	text	NO

 

[Cubist]

Is this what you're looking for?

Book1
	B	C	D	E	F	G	H	I	J	K
1							Game 1	Game 2	Game 3	Game 4
2	Team 1	W	L	Team 2		Team 1	W	W
3	Team 3	L	W	Team 4		Team 2	L	L
4	Team 5	W	L	Team 6		Team 3	L	L
5	Team 7	L	W	Team 8		Team 4	W	W
6	Team 9	W	L	Team 10		Team 5	W
7	Team 11	W	L	Team 12		Team 6	L	W
8	Team 2	L	W	Team 1		Team 7	L	W
9	Team 13	W	L	Team 8		Team 8	W	L
10	Team 10	L	W	Team 7		Team 9	W
11	Team 14	L	W	Team 4		Team 10	L	L
12	Team 6	W	L	Team 3		Team 11	W
Sheet2

Cell Formulas
Range	Formula
H11:I11,H7:I9,H6,H10,H12,H2:I5	H2	=TOROW(IF(G2=$B$2:$B$12,$C$2:$C$12,IF($E$2:$E$12=G2,$D$2:$D$12,NA())),2)
Dynamic array formulas.

 

[Cubist]

If your data are numbers then try this.

Book1
	B	C	D	E	F	G	H	I	J	K
1							Game 1	Game 2	Game 3	Game 4
2	Team 1	8	9	Team 2		Team 1	L	L
3	Team 3	8	1	Team 4		Team 2	W	W
4	Team 5	3	1	Team 6		Team 3	W	W
5	Team 7	6	7	Team 8		Team 4	L	L
6	Team 9	2	7	Team 10		Team 5	W
7	Team 11	2	2	Team 12		Team 6	L	L
8	Team 2	7	2	Team 1		Team 7	L	W
9	Team 13	5	1	Team 8		Team 8	W	L
10	Team 10	4	10	Team 7		Team 9	L
11	Team 14	9	3	Team 4		Team 10	W	L
12	Team 6	6	10	Team 3		Team 11	T
Sheet2

Cell Formulas
Range	Formula
H11:I11,H7:I9,H6,H10,H12,H2:I5	H2	=LET( t,IF($C$2:$C$12>$D$2:$D$12,"W",IF($C$2:$C$12<$D$2:$D$12,"L","T")), tt,IF(t="W","L",IF(t="L","W","T")), TOROW(IF($B$2:$B$12=G2,t,IFS($E$2:$E$12=G2,tt)),2))
Dynamic array formulas.

 

[arun_eagertolearn]

Hi

Cubist's method is definitely going to work

Please find an alternate method, which is slightly easier to decipher, but has an intermediate step

This involves finding the combo of Team-Game no combo - eg: Team 1 Game 1, Team 2 Game 1 etc and assigning, W (Win), Loss (L) and Tie (T) to these
These are depicted in col F to M

A vlookup of this combo will yield the result- in col O to S

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1							Game No	Concat	Result		Game No	Concat				Game 1	Game 2	Game 3	Game 4
2	Team 1	1	0	Team 2		Team 1	1	Team 1 Game 1	W	Team 2	1	Team 2 Game 1	L		Team 1	W	W	W	T
3	Team 3	1	2	Team 4		Team 3	1	Team 3 Game 1	L	Team 4	1	Team 4 Game 1	W		Team 2	L	L	L	NP
4	Team 5	2	2	Team 6		Team 5	1	Team 5 Game 1	T	Team 6	1	Team 6 Game 1	T		Team 3	L	W	NP	NP
5	Team 2	0	1	Team 1		Team 2	2	Team 2 Game 2	L	Team 1	2	Team 1 Game 2	W
6	Team 2	0	1	Team 3		Team 2	3	Team 2 Game 3	L	Team 3	2	Team 3 Game 2	W
7	Team 4	0	1	Team 1		Team 4	2	Team 4 Game 2	L	Team 1	3	Team 1 Game 3	W
8	Team 1	1	1	Team 3		Team 1	4	Team 1 Game 4	T	Team 3		Team 3 Game	T
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=A2
G2:G8	G2	=COUNTIF(F$2:F2,F2)+COUNTIF(J$2:J2,F2)
H2:H8,L2:L8	H2	=F2&" "&"Game "&G2
I2:I8	I2	=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",IF(B2>C2,"W",IF(B2<C2,"L","T")))
J2:J8	J2	=D2
K2:K7	K2	=COUNTIF(J$2:J2,J2)+COUNTIF(F$2:F2,J2)
M2:M8	M2	=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",IF(B2>C2,"L",IF(B2<C2,"W","T")))
P2:S4	P2	=IFERROR(VLOOKUP($O2&" "&P$1,$H:$I,2,0),IFERROR(VLOOKUP($O2&" "&P$1,$L:$M,2,0),"NP"))