Help with Vlookup

[Tridiro]

Hello Everyone,
I need to get excel to deliver a sum from 75 different arrays that are spreed out over 15 separate worksheets (in the same workbook ).

My problem is this, Each array will have a slightly different set of values in the far left column; which returns the error "A value is not available to the formula or function".

In Plain English, If BOB, TOM and SUE are in the left column in the first array, and BOB, BILL and Sue are in the first column of the second array; then the error occurs when I search for TOM because he is not in the second array.

This is the formula i am using.

{=VLOOKUP($A$12,table1,3,0)+VLOOKUP(A12,talbe8,3,0)+VLOOKUP(A12,table3,3,0)}

Please help! Im so Stuck, I need a workaround!

 

[Domenic]

If each table is in fact named table1, table2, table3, through table75, try...

=SUMPRODUCT(SUMIF(OFFSET(INDIRECT("table"&ROW(INDIRECT("1:75"))),,,,1),A12,OFFSET(INDIRECT("table"&ROW(INDIRECT("1:75"))),,2,,1)))

Otherwise, list the name of your tables in a range of cells, let's say G1:G75, then try...

=SUMPRODUCT(SUMIF(OFFSET(INDIRECT($G$1:$G$75),,,,1),A12,OFFSET(INDIRECT($G$1:$G$75),,2,,1)))