# To many vlookup's is there another way

##### Well-known Member
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?

Code:
``=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))``

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Biz

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

Biz

##### Well-known Member
Have not used that formula before but will look into it, thanks

#### Haseeb Avarakkan

##### Well-known Member
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.

##### Well-known Member
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

##### Well-known Member
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")))

##### Well-known Member
I do not, I will give it a try

##### Well-known Member
Awsome, thanks so much Haseeb it is exactly what I needed.

Replies
4
Views
508
Replies
1
Views
342
Replies
21
Views
661
Replies
0
Views
390
Replies
8
Views
421

1,191,183
Messages
5,985,172
Members
439,944
Latest member
Vangelis74

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back