Nested Vlookups
Posted by Gavin Teer on August 06, 2001 1:49 PM
I'm currently working on a fantasy football sheet to calculate out team points per week.
The formula below is to calculate a week's points for 1 team by looking up each player's points for that week and adding them all together.
How do I reduce the size of the this formula:
=VLOOKUP(VLOOKUP($S3,tm,3,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,4,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,5,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,6,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,7,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,8,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,9,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,10,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,11,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,12,FALSE), PTS1,T$2+2,FALSE) + VLOOKUP(VLOOKUP($S3,tm,13,FALSE), PTS1,T$2+2,FALSE)
Posted by lenze on August 06, 2001 2:17 PM
I would suggest you arrange your table to accommodate a Pivot Table report. For example, you might have these columns PLAYER;TEAM;WEEK NO(OR DATE);POINTS. With this you can use a Pivot Table to get Team Totals by week, Team Totals YDT, Player Totals YTD, etc.
