openoffice-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "johnny smith" <ka...@krovatka.su>
Subject Re: [OO] VLookup question with multiple columns
Date Thu, 20 Feb 2014 19:34:48 GMT
On Thu, 20 Feb 2014 00:23:17 -0000, Wade Smart <wadesmart@gmail.com> 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


Mime
View raw message