Simplify a Formula

Thanks for your help I am amost there, by amending your formula to:

IF(Z46="","",VLOOKUP(Z46,IF(Z46<15, Draw1!Y15:Draw1!Z21,Draw1!AF15:Draw1!AG21),2,0))

If I am in col Z46:52 I can get anything in col Z15:21 to work but not in AG15:21 and vice versa, maybe again I am not sure if I made it clear that either of the 2 cols can contain any of the 1-14 names.

Let me get this straight....

You mean all 14 numbers will be somewhere in Y15:Z21 or AF15:AF21 but not in any particular order? If so perhaps try

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",VLOOKUP( Z46,$Y$15:$Z$21,2,0),VLOOKUP(Z46,$AF$15:$AG$21,2,0)))

Sorry daddylonglegs no it doesn’t work and I wonder if I am still not making it clear.

OK in Sheet1 col(s) Z15:21 & AG15:21 are 14 names and alongside them in Y15:21 are fixed numbers 1,3,5,7,9,11,13 & in AF15:21 are 2,4,6,8,10,12,14 which will always identify these names throughout the competition and by inserting a different numbers in sheet2, Z46:52 & AG46:52 I want to insert the appropriate name into the cell where the formula will be throughout each round so they will play a different person each round.

I came up with;

IF(Z46=1,Game1!Z15,IF(Z46=3,Game1!Z16,IF(Z46=5,Game1!Z17,IF(Z46=7,Game1!Z18,IF(Z46=9,Game1!Z19,IF(Z46=11,Game1!Z20,IF(Z46=13,Game1!Z21,IF(Z46=2,Game1!AG15,IF(Z46=4,Game1!AG16,IF(Z46=6,Game1!AG17,IF(Z46=8,Game1!AG18,IF(Z46=10,Game1!AG19,IF(Z46=12,Game1!AG20,IF(Z46=14,Game1!AG21,""))))))))))))))

but would need this formula amended 14 times but is so time consuming and anyway it didn’t work.

I do thank you for all your effort and hope I am not taking up too much of your time.

Both work, brilliant! someday I will figure out how the formula works.

Thanks again for all your help.