=if(C24 ='Ability Base'!$A$2;sum('Ability Base'!$B$2);
if(C24 ='Ability Base'!$A$3;sum('Ability Base'!$B$3);
if(C24 ='Ability Base'!$A$4;sum('Ability Base'!$B$4);
if(C24 ='Ability Base'!$A$5;sum('Ability Base'!$B$5);
if(C24 ='Ability Base'!$A$6;sum('Ability Base'!$B$6);
if(C24 ='Ability Base'!$A$7;sum('Ability Base'!$B$7);
if(C24 ='Ability Base'!$A$8;sum('Ability Base'!$B$8);
if(C24 ='Ability Base'!$A$9;sum('Ability Base'!$B$9);
if(C24 ='Ability Base'!$A$10;sum('Ability Base'!$B$10);
if(C24 ='Ability Base'!$A$11;sum('Ability Base'!$B$11);
if(C24 ='Ability Base'!$A$12;sum('Ability Base'!$B$12);
if(C24 ='Ability Base'!$A$13;sum('Ability Base'!$B$13);
if(C24 ='Ability Base'!$A$14;sum('Ability Base'!$B$14);
if(C24 ='Ability Base'!$A$15;sum('Ability Base'!$B$15);
if(C24 ='Ability Base'!$A$16;sum('Ability Base'!$B$16);
if(C24 ='Ability Base'!$A$17;sum('Ability Base'!$B$17);
if(C24 ='Ability Base'!$A$18;sum('Ability Base'!$B$18);
if(C24 ='Ability Base'!$A$19;sum('Ability Base'!$B$19);
if(C24 ='Ability Base'!$A$20;sum('Ability Base'!$B$20);
if(C24 ='Ability Base'!$A$21;sum('Ability Base'!$B$21);
if(C24 ='Ability Base'!$A$22;sum('Ability Base'!$B$22);
if(C24 ='Ability Base'!$A$23;sum('Ability Base'!$B$23);
if(C24 ='Ability Base'!$A$24;sum('Ability Base'!$B$24);
if(C24 ='Ability Base'!$A$25;sum('Ability Base'!$B$25);
if(C24 ='Ability Base'!$A$26;sum('Ability Base'!$B$26);
if(C24 ='Ability Base'!$A$27;sum('Ability Base'!$B$27);
if(C24 ='Ability Base'!$A$28;sum('Ability Base'!$B$28);
if(C24 ='Ability Base'!$A$29;sum('Ability Base'!$B$29);
if(C24 ='Ability Base'!$A$30;sum('Ability Base'!$B$30);
if(C24 ='Ability Base'!$A$31;sum('Ability Base'!$B$31);
if(C24 ='Ability Base'!$A$32;sum('Ability Base'!$B$32);
if(C24 ='Ability Base'!$A$33;sum('Ability Base'!$B$33);
if(C24 ='Ability Base'!$A$34;sum('Ability Base'!$B$34);
if(C24 ='Ability Base'!$A$35;sum('Ability Base'!$B$35);
if(C24 ='Ability Base'!$A$36;sum('Ability Base'!$B$36);
if(C24 ='Ability Base'!$A$37;sum('Ability Base'!$B$37);
if(C24 ='Ability Base'!$A$38;sum('Ability Base'!$B$38);
if(C24 ='Ability Base'!$A$39;sum('Ability Base'!$B$39);
if(C24 ='Ability Base'!$A$40;sum('Ability Base'!$B$40);
if(C24 ='Ability Base'!$A$41;sum('Ability Base'!$B$41);
if(C24 ='Ability Base'!$A$42;sum('Ability Base'!$B$42);
if(C24 ='Ability Base'!$A$43;sum('Ability Base'!$B$43);
if(C24 ='Ability Base'!$A$44;sum('Ability Base'!$B$44);
if(C24 ='Ability Base'!$A$45;sum('Ability Base'!$B$45);
if(C24 ='Ability Base'!$A$46;sum('Ability Base'!$B$46);
if(C24 ='Ability Base'!$A$47;sum('Ability Base'!$B$47);
if(C24 ='Ability Base'!$A$48;sum('Ability Base'!$B$48);
if(C24 ='Ability Base'!$A$49;sum('Ability Base'!$B$49);
if(C24 ='Ability Base'!$A$50;sum('Ability Base'!$B$50);
"Not Ability")))

what is the reason of summing 1 cell? – wilson Oct 8 '10 at 3:36
This is the simplified version of the formula you provided in the question:
=IF(ISNA(VLOOKUP(C24,'Ability Base'!A$2:B$50,2,FALSE)),
"Not Ability",
VLOOKUP(C24,'Ability Base'!A$2:B$50,2,FALSE)
)
Maybe you can tell your requirement in words if the above is not suitable.
(Tested in Excel 2003)