How to lookup result of game and add results to table

[xan34]

Hello everyone,

I am trying to teach myself some new excel functions and decided to track the first few days of the MLB season this year. I have included all of the games played for the first 5 days of the season. I am looking to see if there is a way to have a table that automatically lists a "W" for a Win or "L" for a Loss for each game per team.

I will manually enter in each games results. I'm then hoping the table can somehow lookup when a team is listed, if their score is greater or less than their opponent, enter in the result as W or L, and then move to the following row for the next game that team plays.

In the attached I already manually added the first 3 days worth of games to the W/L table with all of the teams listed across the top.

Any help is appreciated, thanks!

[xan34]

Ok maybe slightly easier / less daunting question. Worked on it some more today and managed to use the Match function to have the sheet so it pulls the row for each time a team plays away and for when they play at home.
From there, I used the Index function to pull the date of the game, the teams score when they are away, their opponents score, and then calculate if they won or lost. Seperate table created to
calculate the same thing when the team is home.

My question now, is there a way to combine these different calculations / tables into one list that will continue the calculations as new games are added but sorted in order?

In the attached example, I ran my new calculations for Arizona to start. They have played a total of 5 games so far, 4 at home and 1 away.
I would like to combine these two lists somehow so it continues to pull the row, date, and scores but so I can have it in one column sorted by either the date the game took place or its row.

If theres an easier way to do this let me know!

[zakazano]

fairly new to this myself but there's an excellent modeller on YouTube called Riley Wichmann who has probably done this already - or something very similar.

[Fluff13]

How about
In K3
=IFERROR(INDEX(MLB[Date],AGGREGATE(15,6,ROW(MLB[Date])/((MLB[Away Team]="ARI")+(MLB[Home Team]="ARI")),ROWS(K$3:K3))),"")
In L3
=IFERROR(INDEX(MLB[Away Score],MATCH([@Date]&"|ARI",MLB[Date]&"|"&MLB[Away Team],0)),IFERROR(INDEX(MLB[Home Score],MATCH([@Date]&"|ARI",MLB[Date]&"|"&MLB[Home Team],0)),""))
In M3
=IFERROR(INDEX(MLB[Away Score],MATCH([@Date]&"|ARI",MLB[Date]&"|"&MLB[Home Team],0)),IFERROR(INDEX(MLB[Home Score],MATCH([@Date]&"|ARI",MLB[Date]&"|"&MLB[Away Team],0)),""))

Although you wont be able to sort it.