Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Adding to VLOOKUP

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using the VLOOKUP Formula to add numbers together (from two different tables)only if their job number matches. If the job numbers do not match I want my cell to read the total from my second table. How can I do this?
    Thanks,
    Annie

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-15 16:19, Acody wrote:
    I am using the VLOOKUP Formula to add numbers together (from two different tables)only if their job number matches. If the job numbers do not match I want my cell to read the total from my second table. How can I do this?
    Thanks,
    Annie
    Can you post the VLOOKUP formula that you use along with where the job numbers are to be found?

    Aladin

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =SUM(K31+VLOOKUP(B31,jobnumber,10,FALSE))

    1st Table: (3 Job #s)
    B14:B16

    2nd Table: (3 Job 3s)-only one same # as first table
    B30:B32

    Thanks!

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =if(b31=VLOOKUP(B31,jobnumber,1,FALSE),SUM(K31+VLOOKUP(B31,jobnumber,10,FALSE)),"Not Equal")

    I think...
    ~Anne Troy

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-15 16:35, Acody wrote:
    =SUM(K31+VLOOKUP(B31,jobnumber,10,FALSE))

    1st Table: (3 Job #s)
    B14:B16

    2nd Table: (3 Job 3s)-only one same # as first table
    B30:B32

    Thanks!
    Annie,

    I'm still confused.

    'jobnumber' seems to be a lookup table. What range is it? And how B14:B16 and B30:B32 are related to 'jubnumber'?

    Does K31 hold a value that is computed by a VLOOKUP formula?

    Aladin

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, Thanks for your continued help : )

    The range for "jobnumber" is:
    B9 to K9
    B16 to K16
    (everything in the square above).

    B14:B16 are the job numbers from week one that I have on my timesheet.
    B30:B32 are the job numbers from my second week, and on the second week I added one new job that I did not work on the first week.
    So when I plug in my formula for the new job my cell reads N/A...b/c my formula wants to pull a number from week #1.
    I want my formula to look to week one for a similiar job number, if it does not find one, then add only the hours worked on that job for week two.
    K31 it a sum of the hours I worked on that job for week two. K31 is the cell I want my formula to refer to if week one does not have the same job number as week two.

    Sorry that this is so confusing! : )

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-19 08:31, Acody wrote:
    Aladin, Thanks for your continued help : )

    The range for "jobnumber" is:
    B9 to K9
    B16 to K16
    (everything in the square above).

    B14:B16 are the job numbers from week one that I have on my timesheet.
    B30:B32 are the job numbers from my second week, and on the second week I added one new job that I did not work on the first week.
    So when I plug in my formula for the new job my cell reads N/A...b/c my formula wants to pull a number from week #1.
    I want my formula to look to week one for a similiar job number, if it does not find one, then add only the hours worked on that job for week two.
    K31 it a sum of the hours I worked on that job for week two. K31 is the cell I want my formula to refer to if week one does not have the same job number as week two.

    Sorry that this is so confusing! : )
    It's indeed hard to describe the organization of one's worksheet. I don't think I could have done better. After seeing your worksheet, I can now say what you need:

    In L30 enter:

    =K30+IF(COUNTIF($B$9:$B$16,B30),VLOOKUP(B30,$B$9:$K$16,10,0),0)

    and copy down this as far as needed.

    $B$9:$K$16 is the range which you named 'jobnumber'. I think there isn't much of a reason to use a named range here, but you want to, replace that range by its name.

    Aladin


  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you so much!!!!
    It finally worked!!!
    Yippee, yahoo.
    I am soooo happy!

Some videos you may like

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
  •