Is the a better way than LOOKUP?

[John]

I'm coaching a team of youngsters, and would like to track individual stats
by game (1 worksheet per game) and totals for the season. The only problem I
have is that the batting order is going to change every game (league rule).
Player1 would be in row 1 one day and row 8 the next.

So I thought that I could use the name function for each player and each
stat. Something along the lines of, LOOKUP(Player1, Hits) in the Season Total
sheet. But you can't use the same name across sheets. LOOKUP is also giving
some screwy results! Any chance I can make this work? Thanks!!!!

 

[John]

PS... Using the lookup wizard, modifying it to cover multiple sheets, I got
the following formula which works very well:
=INDEX('Gm1'!$A$2:$P$16, MATCH("Jenna",'Gm1'!$A$2:$A$16,),
MATCH("BB",'Gm1'!$A$2:$P$2,))+INDEX('Gm2'!$A$2:$P$16,
MATCH("Jenna",'Gm2'!$A$2:$A$16,), MATCH("BB",'Gm2'!$A$2:$P$2,))

There is no other options? Thanks again

 

[Bob Phillips]

You got replies to the last time you asked this question.

 

[John]

Thank you Bob. While this is true, and the answer was helpful, it did not
solve the problem I am having. And besides as I mentioned LOOKUP is giving
incorrect values because of other calculations (I think at least).

Also as I have mentioned I have found a solution (see below), but the
formula is combersome especially when all 11 games are included. In fact I
may not be able to complete the formula due to character limits?