Adding cell values found in different rows/columns based on specific values

**johnslayer** · 06-27-2012, 10:05 PM

Esker Point Beach Volleyball League 2012.xlsx

I have a 10 week schedule where Teams are referenced by numbers instead of names on one row, and the row below them are the number of wins. In this particular spreadsheet there are 5 different courts that a team can play on, and every week can be a different court.

I know how to do a VLOOKUP for the team #, but how do I then get the cell value directly below that contains the number of wins. I would also need to know the number of games played in that court for that week.

I may be approaching this entirely wrong, but I started by breaking it down to smaller pieces, total of games won every week(long tedious process) and total number of games played. It's best out of 3, weather permitting, so there may be some weeks were only 1 or 2 games are played.

Playoffs are determined by win pct. and not total wins.

I've attached a sheet with what I have started.

Any help will be much appreciated.

**martindwilson** · 06-28-2012, 03:48 AM

if it is always one below use index match but add 1 to the match.
index(a:a,match("xyz",B:B,0)+1) or from column f in the range a:z index(a:z,match("xyz",B:B,0)+1,6)
i cant actualy see where you want to use it tho on your sheet

**johnslayer** · 06-28-2012, 06:29 AM

Thanks martindwilson for the assist.

Sorry that I didn't state where I wanted to use it.

The following is what I ended up with in order to get it to return the correct values.

=INDEX($D$5:$Q$5,MATCH(A75,$D$4:$Q$4,0))

But I still need a little help with counting the total numbers of game played in a court. For example: In Week 1, I'd like to know how many games were played by team #5(K4). Team #5 played in CT-4, so i'd need to add J5+K5. The formula for this example would go in D93.

The only thing I can come up with is doing a nested "if" statement based on the address ... but not quite sure where to start. Esker Point Beach Volleyball League 2012.xlsx

I've attached an updated sheet.

**Ace_XL** · 06-28-2012, 06:41 AM

Quick question..wouldn't you end up double counting if you added up all the matches played by each team on each court considering each match will have two teams?

**johnslayer** · 06-28-2012, 07:08 AM

Originally Posted by Ace_XL

Quick question..wouldn't you end up double counting if you added up all the matches played by each team on each court considering each match will have two teams?

I can see how you could see that, but since i'd be adding horizontally by team for weeks 1 thru ten, I would then get the total number of games played by that team for the entire year.

If I were adding vertically, then you'd be 100% correct, in week 1 it would look as if 30 games were potentially played instead of 15.