Cup Draw

[Sheepkin_Coat]

My son is doing a soccer league in excel and he whats to do a cup draw, so we used the vlookup routine and got the 1st 32 teams drawn, but can not do the last 32 teams, i have included the sheet with the functions on, can anybody help us please

[zbor]

try this:

=VLOOKUP(ROW(E2)-1;$B$1:$C$64;2;0)&" - "&VLOOKUP(66-ROW(E2);$B$1:$C$64;2;0)

(replace ; with , if needed).

With this you don't need helper column (1,2,3,4,...)
Also, if you need them in two separate columns jut place second VLOOKUP formula in differenzt column...

hte table is set to 64 teams starting in 2nd row of any column... But if you need various number of teams it can be solved...

[ChemistB]

Your formula stops in Cell E32. You need to drag that down.

Also, once you have used the RAND() function to create your ranking. Copy and Paste Special > Values over Column A to prevent them from changing everytime something is changed on the spreadsheet.

Also, but not crucial, your VLOOKUP formula only needs to cover the range of $B$1:$C$64, not N64.
Make sense?

[Sheepkin_Coat]

try this:

=VLOOKUP(ROW(E2)-1;$B$1:$C$64;2;0)&" - "&VLOOKUP(66-ROW(E2);$B$1:$C$64;2;0)

(replace ; with , if needed).

With this you don't need helper column (1,2,3,4,...)
Also, if you need them in two separate columns jut place second VLOOKUP formula in differenzt column...

hte table is set to 64 teams starting in 2nd row of any column... But if you need various number of teams it can be solved...

Tried to use the formula but excel sends up an error in the -1 part of the formula, where am i going wrong, can you show me an example on how it works

[zbor]

Sure...

Cup(1).xls

Don't know what's problem.. work for me...

[Sheepkin_Coat]

Thanks that works, i was doing it all in one cell

[zbor]

But beware if rand() bring you two equal numbers