IF plus VLOOKUP

Hello everyone, first post, I hope I've done it right.

I don't know too much about spreadsheets and I am trying to create a spreadsheet that will produce a fixture list for a pool league. I have (manually) figured out how the fixture will vary based upon the number of teams in any given years league, but I am stuck on a function... could anyone help please.

Into the spreadsheet I am entering a number (between 4 – 20) which is the number of teams playing. From that number I want to lookup a list which is the team names and the order in which they play the fixture, this is one of 34 possible lists from my manually created fixture I created.

I don't know if this is correct, but I was thinking 'VLOOKUP' would be the function to use, if so, I am trying to work out (if possible) a nested IF with a VLOOKUP. Something along the lines of:-

IF my number input cell equals 1 then display list 1 from a VLOOKUP. IF my number cell equals 2 then display list 2 etc etc.

Could anyone tell me if I am approaching this the best way and if so give me an idea of how to format the formula.

Apologies if my terminology is wrong, but any input appreciated.

Thanks
J

Excellent description, based on which, I think youw should almsot certainly use the index function. If you would like my help applying it to your scenario, upload an example workbook.

CC

Hello,

Forget the If portion ... and concentrate only on the Vlookup() function it will produce the expected result ... press F1 for an example ...

HTH

JR,

As I understand it, the OP has multiple different lists from which to vlookup, so the first question is "which list?" and the second is "OK, which one in that list (vlookup)" - these can both be achieved in one index function.

Jo-Jo, if I have understood you right, upload an example as requested, if not, try JR's suggestion, or perhaps explain differently.

CC

Thank you VERY much for the replies,

CC; I'm only (so far, if I have got it right) trying to lookup a choice of complete lists, so I am not too sure if I have confused things and indicated that I then need to extract an item from a list, which I don't need to do.

I must be honest, I don't have a Windows/Excel machine in front of me... it is in for repair. I am currently working on a Linux/OOo/calc machine and hoping to transfer it over when my other PC is fixed.

Would it be okay if I created an OOo example to upload?. I would apologise in advance for any simplisticity in my efforts.

J

Give it a go, I have OOo installed but have never really used it (comfort in the familiar, plus I like VBA). It may make more sense just to wait till your computer comes back of course...

Thank you Charlie, I will try. I think OOo opens in Excel and I am really too much of a dummy to get into VBA.

I'll create an example and upload soon.

Thank you.

Please forgive how simple this example is... I really am new to this!.

I have exported from OOo in .xls format. I hope it works and makes some sense.

Thanks
J

See attached, I kind of read between the lines a bit, but this satisfies the requirement.

I applied a "rounding up" for the team numbers as your 5 teams listing includes team 6 and your 7 teams listing includes team 8, I assume that's just for a "week off" spot?

CC

Wow, thank you very much Charlie,

I've only just downloaded and looked and to be honest I need to study up on how it functions, but it looks good.

Sorry if the example was confusing, it was just a starting point and purely an example.

The duplicate of team play lists is because sometimes there are an odd number of teams in which case (the way I had it) the last team number would aways be a 'bye' eg: if there was seven teams, I planned to use the list for eight teams, but team number eight would always come up in the fixture as a 'bye'... does that make sense (difficult to put in words).

I'm not sure if your formulas rely upon the number of weeks that you added, but the weeks aren't usually double the number of teams, because a team never plays itself... again if that makes sense or any difference.

I'm over the moon and will dig in again tomorrow (2:30 now and I'd lose myself in the looking now).

Thanks again for your help, much appreciated.
J

Good morning,

Charlie, if you (or anyone) read the above and was thinking of changing anything, please don't.

I have thought of a very basic error in my list and I don't want anyone making lots of effort for nothing.

I have to work out how I am going to alternate each team with a home and away match every other week. I also have to plan for when matches are not seven days apart (bank hols etc).

Just didn't want to lead anyone astray.

Thank you.
J

Hi, You can preconfigure your fixings lists to make them alternate home/away.

The other bit depends on your choices.

NB this uses simply the numbers from your fixings sheet as lookups in the output table - this is quite handy.

CC

Hi Charlie & all,

I'm a little stuck again, could you enlighten me please.

I've finished sorting out my complete home/away fixtures and I have been trying to intergrate your formulas into my sheet. Can I ask:-

On your example, if I change the 'number of teams' cell from anything other than 7 I get errors, I also get to many matches if I change this number. If I then change it back to 7 the errors remain or change to other errors (doesn't go back to the '7' state) depending on the number I enter. Is this because I haven't entered all the fixed data?... just wondering before I try putting the formulas in my sheet.

One other thing, can you tell me what effect (if any) the new cell 'number of weeks' has on the formula results?... It seems that the number of weeks is a doubling of the number of teams, which isn't quiet right.

Thanks again for any help.
J.

Hi,

First, my apologies to everyone, especially Cheeky Charlie. I think I have started out on the complete wrong track (even the title) with this thread. Did I get it wrong, because it now dawns on me that I require a HLOOKUP and not a VLOOKUP!?. Sorry again if I've misled, I'm beginning to go ga-ga over this.

I've attached another example, if anyone could point me in the right direction it would be much appreciated.

My initial problem is still that I can't work out how to lookup a selective list.

I tried integrating the formula that Charlie kindly supplied (adapting VLOOKUP to HLOOKUP) and failed. I think I also hit a wall were dates are calculated in the formula.

I need to set a start date and then increment by seven days so that matches are played once a week, but I also need the flexibility to change any given date in case it falls on a Bank Hol. I was going to do this by simply adding 7 days to each date so that if one changed I could just over-type the formula, but I'm not sure if/how I should mess with Charlie's nice formula.

If anyone has any thoughts/suggestions, it much appreciated.

Thanks,
J.

Thanks Charlie,

The problem with moving my data across to your sheet is, I originally supplied an 'example', not a wip or proposal (I didn't think anyone would be so kind as to work on it). So now I have to figure out if I can merge your file and my actual working (IP) file.

I'm going to have another go, before giving up.

J.

Charlie,

Please no rush, just whenever you get time. I've been working on this for weeks (with manually sorting the fixture (didn't know if there was an easy 'spreadsheet' way)) and have moved on greatly with your help over the last couple of days.

I can see it being easier to solve than explain to people, I just like to learn a little if/when I can.

Without solving and if you get a chance, is it possible to say from my last upload if I messed something up in your formula?

Many thanks,
J.

Hi JoJo,

Just checking - this does do what you need right?

We are on the same page with byes for odd team numbers - that is why when you say 5 teams it makes space for 6 - just put team 6 name to "Bye" and you're away.

On to the formulae:
Clearly there are no formulae on Sheet5 - just two tables

Number of weeks - this is referenced from the teams_weeks_games per week table with a vlookup - we use it to extend the lookup reference as far as the number of rows we need for all the matches.

Games per week - same as above, same reason, but we also need this to tell us how far apart to put the dates (column f) - you need a sufficient number of rows between each week to fit the right number of matches in.

Team number references (column B) - this is just a simple formula that rounds the number of teams up to the nearest even number, then displays each number - it references row() so as I copy it down, the number is one more each row... clever.

Team names you add yourself, I have just added a little conditional formatting to highlight "missing" and "not used" team names.

The dates:
=IF(AND(MOD(ROW(1:1),games)=0,ROW(1:1)<weeks*games),OFFSET(F19,-games,0)+7,"")

first:
MOD(ROW(1:1),games)=0
Divide an increasing number by the number of games per week - if it divides perfectly, the increasing number is a multiple of the number of games per week - so we want to display a date on this row (if two games per week, every other row needs to be a date, for example)

ROW(1:1)<weeks*games
The total size of the table (again measured with an increasing ROW() reference) must be equal to the number of weeks played * the number of games per week (obviously?) - so don't show us anything after that.

So:
AND(MOD(ROW(1:1),games)=0,ROW(1:1)<weeks*games)
Sets the row-density of the dates and limits the table to the total number of games (games per week * weeks)
then all we need to do is, if it is a "row with a date":
OFFSET(F19,-games,0)+7
add 7 (days) to the last date - which will be the number of games per week above the cell (F19 refers F19 i.e. itself)

To adjust this (holidays etc.) I would simply recommend adding a column for "revised date" - accomodating holidays is easy enough, but adding 8 days a subsequent week because you moved it backwards for another... no thanks

Finally - the big one (although not too long - good formulae don't need to be long!)
=IF(ROW(2:2)<=weeks*games,VLOOKUP(INDEX(matches,ROW(4:4),2+(teams-4)*4),TeamNames,2,0),"")

=IF(ROW(2:2)<=weeks*games,<stuff>,"")
only do anything if within the size of the table (like the dates one)

<stuff>:
VLOOKUP(<team number>,TeamNames,2,0)
When we find the team number look it up in the team names table (B18:C37)

<team number>:
INDEX(matches,ROW(4:4),2+(teams-4)*4)
index says look in a table (matches) pick out row y and column x -
matches is the big table
row(4:4) is the same thing we've seen before, it just starts at row 4 because your team references start on row 4 of the big table - as the formulae copy down, they each look at the next row down
for the column reference, we know the table has 4 columns for every team (starting at 4) so we need to multiply the number of teams by 4 to get the "start point" for the row, then we just need a reference for which column within the four - the first team is the second column of the set of four, the second team is the third column - that is the only difference between the two formulae.

I think that's it, get back to me with more questions

CC

Hi Charlie,

“Just checking - this does do what you need right?”

I'm not sure how to answer that Charlie... I have two problems, but I don't know if I created one of them!:-

1.after I (so it could be something I've done) added the extra data as per your instructions, I get errors. These errors seem to occur when the 'number of teams' are changed and consist of some 'dates' not showing at all, some dates showing something like $VALUE! And also too many team names being listed
2.It needs to have the facility to exclude (or manually adjust without destroying the formula) dates if they fall on a bank holiday or such (the league runs over xmas etc when there are no matches)

“We are on the same page with byes for odd team numbers - that is why when you say 5 teams it makes space for 6 - just put team 6 name to "Bye" and you're away.”

To try to explain... when I originally uploaded my example, it was only that an example, which I didn't expect anyone would be kind enough to adjust (but many thanks). Anyway, on Sheet5 the lists are all duplicated in pairs, eg: '5 Teams' is exactly the same as '6 Teams', '7 Teams' is the same as '8 Teams', '9 Teams' the same as 10 etc etc etc. This duplication was only done for the example. What I planned to do was remove all of the odd team lists in Sheet5. This would mean that if we have a year where there are an odd number of teams, I would use/reference the even lists for the calculations/formula, but I would name the last odd team “Bye”. This would mean that one team would have 'Bye' against it (in the final play list it would show eg: Team 16 plays Bye). So in effect, the odd listings in Sheet5 could realistically be redundant/removed. Does that make sense?

Those are the only things I can think of. All I think I can ask is if you think I have created the first problem and if you think I can adjust your formula for the other things.

I really do thank you for taking the time to explain, I am going off to study... another late night/morn

Thanks again Charlie,
J,

Good morning Charlie & all,

As I was working through your explaination I saw your mention of the dates and the difficulty, so apologies for mentioning that again. I think I might have an option, but at the moment I am just trying to break the formula down by creating non nested formulas that do the individual functions... just so that I can learn (not to actually use in the spreadsheet).

Must admit, you guys are clever and must have put in a lot of studying, I keep finding myself going around in circles and getting confused, but it is good trying.

J.

One thing you mayfind useful:
Tools -> Formula Auditing -> Evaluate formula
with this you can step through the formula - it shows you what is about to be evaluated, and what has just been evaluated at each stage.

Try it.

Yah, I think it's compatibility, see screencap.

Thanks Charlie,

I can get the same results as your screen capture shows, but if I then change the number from 4 to anything else, it goes wrong... unless I save/close and re-open when it then picks up the changes.

As you say, probably an incompatibility between OOo and Excel, so as it's a pool league fixture, I can consider myself well and truly snookered.

I'll see if a friend has Excel and OOo installed and test the compatibility theory in a day or so, but I think this is the end of the road for this project.

All I can do is SINCERELY thank you for all of your time and effort, I can't express how grateful I am.

Thank you VERY much Charlie.
JoJo.

Hi all,

Charlie, for info, I tried your sheet on a friends Excel machice and it works with no problems. So definately a compat issue.

I've asked the OOo forum if they can spot what Calc doesn't like about it.

J.

Hi all,

Just in case anyone looks at this thread at a later date (not that it is an Excel issue).

The eventual problem turned out to be an incompatibility between Excel and the Ubuntu version of OOo-Calc. It appears (although no one can quantify) that the Ubuntu version of OOo-Calc can confuse the 'seperators' (commas v semi-colons), possibly related to locale settings.

Changing from the Ubuntu to the standard Sun version of OOo resolved the problem.

Just for info.
J.

Thanks for returning this valuable information to the board, J.

You're more than welcome Charlie. I hate coming to the end of a thread without a solution or acknowledgement... like a book with the last page missing.