VLOOKUP / IF formula - HELP!

[agarford]

Hi,

I haven’t used VLOOKUP or IF formulas for some time now, but find myself in need of one! My colleague and I are developing a site that utilises football (or soccer!) result data. We have found a supplier who can issue us with an Excel sheet comprising all previous football results from the season. This data is presented to us as the attached Excel document.

The columns consist of HOME TEAM, AWAY TEAM, HOME TEAM GOALS and AWAY TEAM GOALS. We would like to develop a formula that is able to ‘Look Up’ historic goals scored data, and display it against the forthcoming games schedule. For example, we would like to be able to have the forthcoming fixtures in a list like so…

AGS Arsenal v Everton AGS
AGS Liverpool v Tottenham AGS
AGS Stoke v Fulham AGS

….and so on

Next to each team (shown above as AGS), we would like to display the specific teams AVERAGE GOALS SCORED in their previous game. We have all the raw data from our supplier, we just require a formula to help us harvest it!

Any help would be greatly appreciated

[Fett2oo5]

Do you have an example of the setup?

Edit Post → Go Advanced → scroll down to Manage Attachments

[agarford]

Yes, attachment is now added.

[Fett2oo5]

Well this isn't as pretty as you would like, I'm sure, but it gets the job done.

What I did was created a "match-up" table so you can select the team you wish to pair up. (yellow cells)

Out of the data range that was provided [C2:F297], I created two pivot tables that average the team's scores both at home and away.

Depending on the teams you select in the "Match-up" their respective average score will appear next to them. This is done with a HLOOKUP from the pivot tables.


I'm sorry this isn't more elegant. Perhaps someone with more experience will be able to create what you are looking for.

[Fett2oo5]

I'll try to come up with something that is easier and more elegant. You should prob continue to ask for assistance because you need this for a site, and want it to work as smoothly as possible.

Perhaps the talented people in the Excel Programming / VBA / Macros Sub-forum.

[agarford]

Thanks so much for taking the time to help! Really appreciate it.

This is perfect to start us off!

What we would ideally like now, to take us to the next level, is something like this…

In addition to average goals per game data that you have kindly assisted with, we would like to be able to illustrate each teams ‘previous 5 game goal record’. We would like to show these records next to the team name. So we are looking to develop a formula that will automatically look up the previous 5 game scoring record, for both the home and away teams, from the raw data, and then convert this data to illustrate it using football logos embossed with the score (as shown in the attached word doc).

Does this sound possible? If so, any help would be greatly appreciated!

Below is an example of what we envisage… (plus a key)

AGS 5 4 3 2 `1 Arsenal v Chelsea 1 2 3 4 5 AGS

AGS = Average Goals Scored (already done)
1-5 = The goals scored in the past 5 games


Look forward to hearing from you

[Fett2oo5]

Sorry I wasn't able to respond quickly I had a lot going on today at work.

Thanks to some research and a lot of help from forum member: popipipo in this thread: Find 5 latest dates and return values for each I was able to put this together.

I hope it is what you are looking for?

[agarford]

That’s brilliant. Exactly what we had in mind. I have made a few comments on the attached document – of which are summarised below.

- We plan to have the top 5 leagues of English football for our users to select from. So we need to apply the same formulas that you have kindly provided for the Premier League for the other 4. I have attached the raw data on new sheets within the attached document, should you be kind enough to assist.

-When I copy the fixture row down, an error in the AGS column appears. Am not sure why this is.

-Have added a Premier sheet too (currently blank), so that Premier score data can be transferred onto this tab for ease of maintenance in the future.

So a few additions/tweaks and we are there.

Again, we can’t thank you enough for your assistance.

[lfrancisco]

I haven't had much time to look, but the quick solution to this:

-When I copy the fixture row down, an error in the AGS column appears. Am not sure why this is.

The data the HLOOKUP was pulling from was not "locked". Replace the following:

Cell B6 Old --

Please Login or Register  to view this content.

Cell B6 New --

Please Login or Register  to view this content.

Cell P6 Old --

Please Login or Register  to view this content.

Cell P6 New --

Please Login or Register  to view this content.

After you fix those two cells/formulas, you can then copy them down to new rows without issue.

[agarford]

Great, thanks for your help.

Have made the changes you provided (see attached document).

Now all we require assistance with is setting up formulas like we have for the Premier League, for the other 4 Leagues. Any help would be kindly appreciated.

[Reykjavik]

This look nice, and putting this in one document would be cool.

Maybe this will make something extra.

I have bymyself some statistics, but it often quite difficult to make it useful. Where did you get your data so clear?

Good luck.

[agarford]

We obtained our data from http://www.football-data.co.uk/

[popipipo]

I keep it short (because english is not my native language)
Look at the yellow cells

If you need more explanation i will try

[agarford]

Thanks for your reply, I am still unsure how to produce the correct formulas for what we need. The attached document has comments on saying what we need help with.

Again, any help is greatly appreciated.

[ratcat]

G'day

Does this attachment help ?

The Premiership League on the data tab is working. With drop down menu for the teams and what was the last five games points score from the last round that's been selected. Cell J3 to change the Round played.

Here is the basics of learning in how to create a drop down menu http://www.contextures.com/xlDataVal01.html

[popipipo]

Howabout this.

[agarford]

Brilliant!

All we need now are a few Pivot Tables, and we are complete! Thankyou so much for your help.

Please find attached Excel document.

[Fett2oo5]

Ok agarford, it took some work and time but attached is the spreadsheet you were looking for. I've doubled checked the stats but if you want you can double check them as well.

I've put instructions for how to add more data on the tabs, basically you will paste it below the current.
Since you are only concerned with the last season I would suggest pasting over the previous season when you get a chance.
The current size is .87 MB the more data and formulas added will increase the file size. If this is of no consequence to you then don't worry about it.

I hope this serves you well. Let me know if you need something else.

---

Please provide feedback (Click *, bottom left of the post), if the suggestion helps you!

[agarford]

Fett2oo5,

Brilliant, thats exactly what we had in mind. I have added in rows so that we are able to look up data for all fixtures from all of the leagues. When doing this, there appears to be a slight glitch in the 'Premier' drop down menus. Several blanks appear. Please refer to the attached document.

Again, we really appreciater your help :-)

[arlu1201]

It has come to our attention you have violated Rule 4 of our Forum RULES. Don't Private Message, Visitor message or email Excel questions to moderators or other members. (Or Access, Word, etc.)

All questions and answers will benefit other posters like yourself. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this rule is considered harrasment by most of our contributors and will not be tolerated. Repeat offense could lead to permanent ban, so do take this caution to heart.

Post your question in a public thread and our many contributors will come to you to assist, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.

[agarford]

Apologies for the violation. It will not happen again.

If anyone is able to help with our previous query, we would greatly appreciate it.

Regards.

[Fett2oo5]

You are having trouble with the blanks for the data validation dropdown menu because the teams are listed in Column Z. When you are inserting rows you are also inserting blank cells in the list of the teams (because Permiership is at the top you are inserting blank cells in the list of Premiership teams.) On the current spreadsheet you have, highlight Columns Y→AA, right click, click Unhide and you will see what I'm talking about.

Take a look at this, I've moved the teams below all the tables so you can complete what you are trying to achieve.

Sorry for the late reply, work has been incredibly busy.