INDEX/MATCH Help!

[Matthew.Newlove]

System.xlsx

Please refer to attachment, I have a football league table showing the number of goals conceded and scored for both home and away games, I have then created formulas to give me the average goals per game for home and away games (the beauty of this document is that I can copy and paste directly from specific websites so the template is universal for all leagues). I have then created a table, which shows home teams on the left column, and the top row shows away teams, the data shows an average of the [average goals scored and conceded for the home teams] and the [average goals score and conceded for the away teams]. I want to be able to copy in upcoming fixtures (again directly from a website), then, if the home team (the first, i.e. Newcastle United (Home) Vs Everton (Away)) appears in my home team column AND the away team appears in my away team row then I want another cell to display the corresponding cell showing the average number of goals scored between the two teams.
On the file, you'll see on the very far right I have copied upcoming fixtures to the far right (columns BD:BG), I want to compare these teams to the teams in column AD and row 2, where BOTH are present, I want to know which result corresponds to both teams, for example, the first fixture, Manchester United Vs Everton, I want it to display cell AK3 value 3.24, does this make sense?

I realize this seems somewhat complicated and long winded. Any help would be very much appreciated, I tried to explain my dilemma clearly, but if anything is difficult to understand or unclear, don't hesitate to let me know and I'll try to rephrase. Thank you

[Haseeb Avarakkan]

Hello Matthew,

As per forum rules duplicate thread are NOT allowed. Since you have an attachment on this thread, send a Private message to any moderator for lock your other thread.

Team names in column BD & AD are not matching,so try this ARRAY FORMULA,

Please Login or Register  to view this content.

Copy this formula then in a cell press F2 on keyboard then press Control+Shift+Enter together. If you did it correctly you can see the formula surrounded by curly brackets {}

Then copy this cell & paste down as needed.

[Matthew.Newlove]

My apologies, I will do. I still don't seem to be getting the results I require. I realise the text isn't identical, I think I will have to edit this manually which isn't a problem. If for example I want to know the average number of goals scored for the first fixture in the list between "Manchester ..." at home and "Everton" away, It should be showing me the result in cell AK3, which is 3.24 at present it isn't showing me this, I'm not entirely sure why. My apologies I'm really showing my excel amateurism here. Also, the ctrl+shift+enter has just put a border on my cells which doesn't want to disappear, but that's the least of my problems at present. Thank you for your time.

[Haseeb Avarakkan]

I am sorry Matthew, I am not following you.

What are you trying to achieve? Do you want to calculate the Avg in AE3:AX22? Or you have already calculated avg in AE3:AX22 & you want to lookup the avg team score based on team entered in BD4 & BF5 in BH5 or any other cell?

[Matthew.Newlove]

The latter, how you explain it seems much clearer the average is already worked out in AE3:AX22, I just want that value to be dropped into another cell which corresponds to the teams in cell BD4 and BF5, columns BD and BF will change, so I'm hoping I can simply copy and paste from a website into those cells and the relevant information is pulled from AE3:AX22 depending on which two teams are in those columns. Again, thank you.

[Haseeb Avarakkan]

...I just want that value to be dropped into another cell which corresponds to the teams in cell BD4 and BF5...

So the formula I suggested in my last reply, should work. See the attached.

[Matthew.Newlove]

Brilliant, works perfectly. Thank you for your help (and patience)