# Help with Vlookup

This is a discussion on Help with Vlookup within the Excel Questions forums, part of the Question Forums category; Hello Everyone, I need to get excel to deliver a sum from 75 different arrays that are spreed out over ...

1. ## Help with Vlookup

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)}

2. ## Re: Help with Vlookup

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)))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•