extract wins / losses from other worksheet

Hi everyone,

The attached file contains wins/losses versus a specific team in line 1 of data. For instance, D1 says 'BOS' so B1 and C1 contain the wins losses of BAL (B3) versus BOS (D1).

FYI: all teams in row 1 are changed to reflect the vs team. All teams in row 3 are permanently there to reflect which team the score is for.

What I would like to do is to be able to do this in the sheet named 'Team W-L'. For instance, to populate 'E6' I need to find where 'LAD' (Z3) is located then find all the times it played versus 'CHC' (AB1) and put the wins in E6 and losses in 'F6'.

FYI: D and G columns determine which team is compared to which in the 'Team W-L' sheet. The results from the 2014-2 sheet go in 'EF' and 'HI' for each respective team. The first row of data is manually done to show the expected result.

The other teams should be populated similarly.
Pedrov664Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ejgil HedegaardConnect With a Mentor Commented:
Guess it is the same file as Team-Loss-in-Red.
Team in row 3 and W/L in row 1 for the selected compare team.
The formula to get the win (E6) is =INDEX('2014-2'!$1:$1,1,MATCH($D6,'2014-2'!$3:$3,0))
Similar for the other, see file.
Team-Loss-in-red.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
Attachment? :-)
0
 
Pedrov664Author Commented:
Glen,

Am I missing something? i.e. attachment missing?

Pedro
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Glenn RayExcel VBA DeveloperCommented:
Yes, the attachment is missing. :-)
0
 
Pedrov664Author Commented:
Glen,

Please post attachment.

Thanks
0
 
Rob HensonFinance AnalystCommented:
Pedrov664 - In your question you quote "In the attached file", Glenn Ray was pointing out that you had omitted to attach a file. How is Glenn supposed to attach the file that you have omitted, it is your file.

Hopefully, Ejgil has made the right assumption regarding the required file in the last comment.

Thanks
Rob H
0
 
Pedrov664Author Commented:
Rob,

Thanks for pointing that out. You are right the same file can be used.

That said ejgil's formula does work.

Thanks,
0
 
Glenn RayExcel VBA DeveloperCommented:
Pedro,  If ejgil's solution works, please properly close this thread by clicking the "Accept this solution" link above his post.

This is the craziest thread...
0
 
Pedrov664Author Commented:
short, sweet and to the point.
0
All Courses

From novice to tech pro — start learning today.