Formula question; multiple lookups?

[Lammers73]

Hello!

I am looking for some help solving this excel puzzle. Thank you for your time!

My goal is to look up a data set and calculate the win% against variable teams.
So Team A vs Team B x%, Team A vs Team C x%, but also team B vs Team C%. So it will result in a matrix with each team in a column and a row.

The data set contains matches registered with a win or lose result.
For example :

Team A vs Team B Win
Team A vs Team C Loss
Team A vs Team B Loss (2nd match)
Team B vs Team D Win
Team B vs Team A Loss
Team B vs Team D Loss
Team C vs Team A Win
etc


Team A has 2 match played vs team B with a win % of 50% out of 2 matches played.

How would you put this in a excel formula?

Kind regards for your help!

 

[bobaol]

Hi, your question is kind of abstract. How about putting each detail in each single cell, then use a formula in each cell. DONT'T nest the formulas, but use one formula for one question, then use another formula for another question, and so on. it is much easier to use one formula in one cell, then based on the results of that cell, use another formula in another cell. that way, it is much easier than trying multiple formulas in one cell. Cheers!

TEAM	Won	lost	TEAM	TEAM
Games			A	B
Played			Won	Won
Against B
44​	11​	33​	0.25​	0.75​