Simplify a Formula

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

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.