Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Help with Vlookup

  1. #1
    Board Regular Tridiro's Avatar
    Join Date
    Apr 2007
    Location
    Louisville
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    17,647
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

User Tag List

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