+ Reply to Thread
Results 1 to 5 of 5

Combining a VlookUp + SUM

  1. #1
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    104

    Combining a VlookUp + SUM

    Here is my problem,

    I need to do a vlookup of a department, and depending on the current month, sum a range.
    Example: Look up Department "100" from "tbl_Salary_Projection", "Sum(Jan, Feb, March)", "False" March needing to be the current month

    I read that you can use an array, but I'm not sure how to make that dynamic to only sum through current month. Example:
    =SUM(VLOOKUP(100, tbl_Salary_Projection, {2,3,4,5,6,7,8,9}, FALSE))

    There may be an alternate way to accomplish this.
    If anyone has any suggestions I am willing to try them.

    Very much appreciated,

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Combining a VlookUp + SUM

    I suggest SUMPRODUCT instead
    With month is 1st day of month (i.e 3/1/2019) formatted as "mmm"
    (B1:M1)
    B7 is lookup value
    Try:
    Please Login or Register  to view this content.
    Untitled.png
    Quang PT

  3. #3
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    104

    Re: Combining a VlookUp + SUM

    Thank you so much for your help,
    When I adapt the formula above, I get a result of 0?

    Here is what my formula ultimately looked like:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combining a VlookUp + SUM

    I believe that the issue is caused by the dates being changed to text when the range is converted to a table.
    A work around could be to insert a row of actual dates above the table (it could then be hidden for aesthetics) and reference that row instead of Table9[[#Headers],[January]:[December]]
    The formula would read something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    104

    Re: Combining a VlookUp + SUM

    PERFECT!!
    I can use this solution in like 10 different situations, thank you so much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combining Vlookup + If with > <
    By Tayga in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-30-2015, 10:48 AM
  2. [SOLVED] Help : Combining Sum If & Vlookup
    By tanfidzularus in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-16-2014, 11:01 PM
  3. Combining VLookup & IF?
    By brianjluke in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2013, 11:19 AM
  4. Combining If and Vlookup..I think?
    By chbrandt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-07-2013, 08:55 PM
  5. Combining IF and Vlookup
    By preveo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2013, 11:01 AM
  6. Excel 2007 : Need help combining vlookup with IF
    By Milkie in forum Excel General
    Replies: 2
    Last Post: 04-19-2012, 02:20 PM
  7. combining vlookup with if?
    By mcarrington in forum Excel General
    Replies: 1
    Last Post: 07-02-2007, 04:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1