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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Attachment? :-)
0
Pedrov664Author Commented:
Glen,

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

Pedro
0
Glenn RayExcel VBA DeveloperCommented:
Yes, the attachment is missing. :-)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Pedrov664Author Commented:
Glen,

Please post attachment.

Thanks
0
Ejgil HedegaardCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.