Clash Royale CLAN TAG

Finding a cell in row, then using that cell reference to find vertical cell within range of cell reference

Sort of new to Excel, so not sure if this is possible.

In Sheet 1 I have the fixtures of teams playing in matchday 1;

A B C D E
----------------------------
Matchday 1
Team 1 - Team 4
Team 2 - Team 5
Team 3 - Team 6


In Sheet 2 I have the previous years fixtures

A B C D E F
----------------------------------------------------------
Team 1 Team 2
Home Team Result Away Team Home Team Result Away Team
Team 1 1-0 Team 2 Team 2 0-1 Team 1
Team 1 1-1 Team 3 Team 2 2-1 Team 3
Team 1 1-2 Team 4 Team 2 0-1 Team 4
Team 1 2-2 Team 5 Team 2 1-1 Team 5
Team 1 1-0 Team 6 Team 2 3-1 Team 6


I want to be able to get the home teams name in Sheet 1, find that teams name in row 2 in Sheet 2, then finding the result based upon who the away team is.

For example, In Sheet 1, I want to find the result of Team 2 at home and Team 5 away from Sheet 2. So finding Team 2 in D1, then finding the result in E6.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.