Simplify a Formula

I have 14 names in individual cells which are identified by the numbers 1 to 14 on page 1 of my spreadsheet (named Game1) and I would like to insert any one of these names on page 2 by inserting numbers rather than typing out the names names using the formula below however is there a way to simplify this formula? Hope you can understand my question.

=if (Z46=””,””)if(Z46=1,Draw1!Z15)if(Z46=2 ,Draw1!Z16) and so on up to number 14

Sorry it’s not working as it returns a “#!Value” but I don't think I have explained my query properly.

On Sheet 1 (Game1) I have in fact 2 cols of 7 individual names in cells Z15 to 21 and another 7 names in AG15 to 21 and alongside these in cols. Y15 to 21 are the numbers 1 to 7 and in AF15 to 21 are number 8 to 14 so that I know that number 5 will always identify say P Walker etc. throughout the competition.

On Sheet 2 (Game2), in cell Z46 I would like, by inserting in say the number 5, that in the adjoining cell AA46 the name of P Walker would appear thereby meaning I wouldn't have to type or cut and paste in his name and of course this would apply to all 14 names and I wondered what the simplest formula would be.

Many thanks for your help.

Try

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

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)))