To many vlookup's is there another way

[jcaptchaos2]

I have this formula that has been working well until now. It will not allow any more vlookup additions. Is there another way to do this?

=IF(ISNA(VLOOKUP($D4,Game1!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game1!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game2!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game2!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game3!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game3!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game4!$C$4:$F$28,2,FALSE)),0,VLOOKUP($D4,Game4!$C$4:$F$28,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game5!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game5!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game6!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game6!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game7!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game7!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game8!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game8!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game9!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game9!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game10!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game10!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game11!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game11!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game12!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game12!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game13!$C$4:$F$29,2,FALSE)),0,VLOOKUP($D4,Game13!$C$4:$F$29,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game14!$C$4:$F$35,2,FALSE)),0,VLOOKUP($D4,Game14!$C$4:$F$35,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game15!$C$4:$F$36,2,FALSE)),0,VLOOKUP($D4,Game15!$C$4:$F$36,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game16!$C$4:$F$31,2,FALSE)),0,VLOOKUP($D4,Game16!$C$4:$F$31,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game17!$C$4:$F$29,2,FALSE)),0,VLOOKUP($D4,Game17!$C$4:$F$29,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game18!$C$4:$F$29,2,FALSE)),0,VLOOKUP($D4,Game18!$C$4:$F$29,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game19!$C$4:$F$26,2,FALSE)),0,VLOOKUP($D4,Game19!$C$4:$F$26,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game20!$C$4:$F$29,2,FALSE)),0,VLOOKUP($D4,Game20!$C$4:$F$29,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game21!$C$4:$F$29,2,FALSE)),0,VLOOKUP($D4,Game21!$C$4:$F$29,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game22!$C$4:$F$33,2,FALSE)),0,VLOOKUP($D4,Game22!$C$4:$F$33,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game23!$C$4:$F$29,2,FALSE)),0,VLOOKUP($D4,Game23!$C$4:$F$29,2,FALSE))+IF(ISNA(VLOOKUP($D4,Game24!$C$4:$F$29,2,FALSE)),0,VLOOKUP($D4,Game24!$C$4:$F$29,2,FALSE))

 

[Biz]

Probably you many want to look into Choose function as you have 32 options in Excel 2003 and Excel 2007-2010 254.

Biz

 

[jcaptchaos2]

Have not used that formula before but will look into it, thanks

 

[Haseeb Avarakkan]

If your tab names are Game1, Game2, Game3..... Game24. try this

=SUMPRODUCT(SUMIF(INDIRECT("Game"&ROW(AA$1:AA$3)&"!C2:C31"),$D4,INDIRECT("Game"&ROW(AA$1:AA$3)&"!D2:D31")))

Note: Your tab name must be same as Game1, Game2, Game3........ Game24. Shouldn't be have any space.

 

[jcaptchaos2]

Haseeb,

I think your suggestion is what I need but need some more help, how does AA$1:AA$3 come into play?

 

[Haseeb Avarakkan]

I am sorry. That should be AA$1:AA$24. This is just using to get the numbers as {1,2,3,4,5....24} Column AA used here, because if we are using non usable range can avoid excel to recalculation. Otherwise excel will recalculate when ever the data changes in the range. I hope you don't have any data in column AA.

The original formula should be,

=SUMPRODUCT(SUMIF(INDIRECT("Game"&ROW(AA$1:AA$24)&"!C2:C31"),$D4,INDIRECT("Game"&ROW(AA$1:AA$24)&"!D2:D31")))

 

[jcaptchaos2]

I do not, I will give it a try

 

[jcaptchaos2]

Awsome, thanks so much Haseeb it is exactly what I needed.