+ Reply to Thread
Results 1 to 7 of 7

Excel 2013: Need solution using VLOOKUP combined with SUMIFS or INDIRECT/MATCH

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    17

    Excel 2013: Need solution using VLOOKUP combined with SUMIFS or INDIRECT/MATCH

    Hi there: I'm finding some good info here, but not quite what I need just yet. I'm attempting to modify a current VLOOKUP formula (that works), but putting the information into one table for easier maintenance. By placing this in a table I've added a column for the Comp plan.

    The current formula is using a range of values to identify if the comp falls within that range, and then the formula provides the Comm %. See example below. However, adding the Comp plan ID, I need something to perform that additional criteria. Don't worry about the UL column (at least I don't think we need to).

    Comp Plan Sales UL Comm %
    SR1 0.00 24,999.99 2.00%
    SR1 25,000.00 49,999.99 3.50%
    SR1 50,000.00 + 4.00%
    SR2 0.00 74,999.99 2.00%
    SR2 75,000.00 124,999.99 2.75%
    SR2 125,000.00 + 3.50%
    SR4 0.00 99,999.99 1.00%
    SR4 100,000.00 199,999.99 2.25%
    SR4 200,000.00 + 3.00%


    So, the formula should take the table above find the Comp plan and the associated Sales amount to compare it with the values that are selected in the main table. For example, SR1 with $85K sales would result in 4%. SR2 with 100K sales would result in 2.75%.

    Please let me know the best way that this is possible. Thanks again for all your help! = )

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Excel 2013: Need solution using VLOOKUP combined with SUMIFS or INDIRECT/MATCH

    Copy-pasting that table into A1:D10 of a spreadsheet.

    Then, the inputs for the lookup operation:
    F1 = "Comp Plan"
    G1 = "SR1"
    F1 = "Sales"
    F2 = 85000

    F3 = "Comm% Output"
    Please Login or Register  to view this content.
    This is an array formula, so it must be entered with CTRL+SHIFT+ENTER.

    This requires that the Sales figures be sorted low-to-high for any give SR1 code, if they are not sorted that way, then the lookup will not return the expected values.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    01-24-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Excel 2013: Need solution using VLOOKUP combined with SUMIFS or INDIRECT/MATCH

    Thanks, Ben! Let me give this a shot. Based on your response, I need to modify or clarify some things.

  4. #4
    Registered User
    Join Date
    01-24-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Excel 2013: Need solution using VLOOKUP combined with SUMIFS or INDIRECT/MATCH

    You rock - it's perfect! As I said, I just had to adjust the cell references a bit, but it's all good. This was my formula with values in cells G2:H4. Only needed Comp Plan and Sales.
    {=INDEX($E$2:$E$10, MATCH(H2, IF(G2 = $B$2:$B$10, $C$2:$C$10, ""), 1))}

  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Excel 2013: Need solution using VLOOKUP combined with SUMIFS or INDIRECT/MATCH

    Thanks for the rep.

    If you're good, don't forget to mark the thread as "Solved."

  6. #6
    Registered User
    Join Date
    01-24-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    17
    OK I'm not really a noob but where do I do that?!

    Quote Originally Posted by ben_hensel View Post
    Thanks for the rep.

    If you're good, don't forget to mark the thread as "Solved."

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Excel 2013: Need solution using VLOOKUP combined with SUMIFS or INDIRECT/MATCH

    Use the menu option Thread Tools which is above and to the right of your first post in this thread, and you can mark the thread as SOLVED from there.

    Hope this helps.

    Pete

+ 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. Combined Vlookup and SumIfs
    By Iappreciate in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-27-2017, 08:07 PM
  2. [SOLVED] Will an INDIRECT formula combined with a VLOOKUP work?
    By DRFJR in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-02-2014, 09:03 AM
  3. [SOLVED] Countifs,Indirect Function and Vlookup combined
    By Yari1986 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 11:19 AM
  4. INDEX , MATCH ,INDIRECT Combined usage example - Pls
    By TKD in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-24-2012, 09:32 AM
  5. INDEX, MATCH & INDIRECT combined usage
    By TKD in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-13-2012, 07:21 PM
  6. Combined vlookup and sumifs in a single formula
    By mrexcelrc1 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-27-2012, 04:54 PM
  7. Replies: 2
    Last Post: 10-30-2011, 05:15 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