Looking up results from a list with two variables

I have a database of football results. In column A I have the Home Team. In Column B I have the Away Team. In Column C is Home Goals, Column D is Away Goals, Column E is Result (Home (H), Away (A) or Draw (D), Column F is the date.

I want to be able to put into a different sheet a fixture, Cell A1 - Home Team XXXX vs Cell B1 - Away Team YYYY, and I want it to pull the most recent 10 games between those 2 teams from the database. If there are not 10, then just all the results, if there are over 10, the most recent.

Here is an example workbook. On Fixtures sheet, in A2 I will put a home team, in B2 Away team, and want it to populate cells C2-L2, most recent first pulling from Column E on results sheet.

Thanks. thats almost it, but instead of pulling the date, i want to pull the info in column E, the H,A or D. Is that possible?

Thanks. thats almost it, but instead of pulling the date, i want to pull the info in column E, the H,A or D. Is that possible?

I tried that and it's just pulling through H, H, H when it should be D, D, D

I didn't see the link - have read it now, appreciate the help, thanks.

Ok, so now I have a similar question. I have tried messing about with the formula but to no avail. I want to bring up the same information, but only searching the team listed in column A, whoever the opponent, and whether they are home or away. So for example

Team listed at home week 1, away week 2, home week 3. Team name is in A3, want it to match and bring up the result for that team whether they are home or away. i guess I need to remove the reference to the team in the second column as before, but it doesn't seem to make any difference. thanks in advance.

Fotis, thanks for your continued help. Apologies for not asking all questions together. I have collated them now with what I am trying to do, and uploaded an example workbook also.

All I will be changing is the values in Column A and Column B, from A2:A151 and B2:B151. I would then like to pull a series of results based on the values of one or both those cells.

Q1) Find most recent 10 results (result field is Database Column E) of team listed in column A, regardless of whether they are home or away.

Q2) Find last 10 home results of team listed in column A.

Q3) Find last 10 results of either combination between team listed in column A & Column B. For example xxxx vs yyyy, or yyyy vs xxxx.

Q4) Sum of columns C&D for either combination of teams eg xxxx vs yyyy or yyyy vs xxxx

Q5) Sum of Column C when Column A is Home team

Q6) Find results of last 5 games for Column C, where team in A2:A151 is either home or away (similar to Q1)

Q7) Find results of last 5 games where A2:A151 is the home team only.


They are all my questions, I am sure they are variations of each other, I am just not sure how to reference a single team, a combination of 2 teams or the variable where an individual team is home or away.

Again, many thanks in advance for your help.

Here is the example sheet

Thank you Fotis, this is amazing, that is exactly what I was looking for.

With regard to Q3, in the example sheet I am looking for the last 10 results of either Leierse v Haralbeke, or Haralbeke v Leierse. Effectively the result of the last 10 times they have met. I think its probably a variation of Q1 adding in the second team variable, but I am not sure.

Thank you so much for your help, if we can get the answer to 3 then I am good to go!

Actually Fotis, I just noticed an issue. You are pulling the dates as the results in column F of the results tab. I actually need to pull the data in column E. I will have to reference the date though to get the most recent. Sorry for the misunderstanding.

perfect, thank you!

Ok, so having applied all the formulas and played around with them, I cannot get them to work and dont know why, so I am uploading the sheet exactly as it will be in my workbook, with references from which questions apply to which result set. I am looking for the result, not the date to be pulled through.

Hopefully its just something minor I am missing, and we can get this resolved finally.

Thank you for your continued help.

No problem, I understand. Thanks for all your help and have a great holiday.

Anyone? Can anyone help pick up Fotis' work and help me get this finished please?

Fotis, Any chance you could take a look at this please. thanks.