Thread: 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.