On Thu, 20 Feb 2014 00:23:17 -0000, Wade Smart <[email protected]> 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: [email protected]
For additional commands, e-mail: [email protected]
