On Thu, 20 Feb 2014 00:23:17 -0000, Wade Smart wrote: > =VLOOKUP(\$E18,\$Teams.\$A\$12:\$I\$23,(\$G\$10+1),0) > > E18 = Player Number on Roster (1 to 12) > Teams.A12:I23 = Team Roster names > G10+1= the input of team number > > So we have Teams A 12 to 23 > 1 > 2 > 3 etc > > Teams B > player name > player name > etc > > Teams C > player name > etc > > and I want to do this: > Teams A, B, C, D, E > 1, 14, name, 2, name > 2, 4, name, 5, name > etc. so, you need to do lookups not only by number from column a, but also according to an alternative numbering system, whose numbers are indicated in columns b, d, f etc.? if i have understood everything right, then the result should be like this: "roster no.","jersey no.","team no. 1","jersey no.","team no. 2" 1,2,"alan",3,"frank" 2,1,"bruce",5,"gerald" 3,5,"charlie",2,"henry" 4,3,"daniel",1,"ian" 5,4,"elton",4,"james" ,,,, "numbering system","team no.","player no.","name", "roster",1,3,"charlie", "jersey",1,3,"daniel", "roster",2,3,"henry", "jersey",2,3,"frank", (above is a csv file, lines are rows, and comma-separated numbers and quoted strings are cells.) the formula for column d, 'name', of the lower table is: d9=VLOOKUP(C9;IF(A9="roster";INDIRECT("R2C1:R6C"&(2*B9+1);0);INDIRECT("R2C"&2*B9&":R6C"&2*(B9+1);0));IF(A9="roster";2*B9+1;2);0) from d10 downwards - just expand d9. you need only to substitute the appropriate cell ranges. ------------------------------------------- List Conduct Guidelines: http://openoffice.apache.org/list-conduct.html To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org For additional commands, e-mail: users-help@openoffice.apache.org