Lookup & Ranking

Put this in E2:

=INDEX(A:A,MATCH(D2,$C:$C,FALSE))

then copy down.

Alternatively, you could do away with column D and use this in E2:

=INDEX(A:A,MATCH(ROWS($1:1),$C:$C,FALSE))

Hope this helps.

Pete

That is great thank you.

Is there any way i could control the randomise bit. so it only randomises the teams on a command?

One way to do it would be to set the Calculation Option on the Formulas tab to Manual, and then if you want to generate another list you can just press the F9 key.

Another way (without using VBA) would be to use a cell to indicate that you want to change the random numbers (and thus the order of the teams). For this you need to enable iteration - click on the File button, then Excel Options at the bottom of the panel, then click on Formulas, and then click to Enable Iterative Calculation, then OK. Then you can use this formula in B2:

=IF($H$2="Change",RAND(),B2)

and copy this down. This assumes that you are using H2 as the indicator - put the word "Change" in that cell to recalculate everything, or anything else (or delete it) to remain the same. You could set up Data Validation on H2 so you could pick from Change or Remain, or whatever.

Hope this helps.

Pete