Results 1 to 2 of 2

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. #1
    Board Regular Tridiro's Avatar
    Join Date
    Apr 2007
    Location
    Louisville
    Posts
    97

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

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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,343

    Default 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)))
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com