+ Reply to Thread
Results 1 to 3 of 3

Lookup based on three criteria

  1. #1
    Registered User
    Join Date
    12-17-2013
    Location
    Greater NYC Area
    MS-Off Ver
    Excel 2010
    Posts
    84

    Lookup based on three criteria

    Hey all,

    I've got a lookup question for you. I've got a table with a set of options and their corresponding data points. I want a formula outside of the table to return one of these data points based on a series of toggles that the user can manipulate. I've gotten 2/3 of the way there by using a sumifs formula, but I'm not sure how to take in to account the third lookup criteria - namely if the size entered by the user is between or over a certain threshold.

    The spreadsheet does a pretty good job of explaining what I'm after.

    Thanks for your time.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Lookup based on three criteria

    =SUMIFS(MgmtFeesData[% During First Stage],MgmtFeesData[Series],$M$8,MgmtFeesData[Options],$M$7,MgmtFeesData[SizeMin],"<="&M6,MgmtFeesData[SizeMax],">"&M6)
    =SUMIFS(MgmtFeesData[% Thereafter],MgmtFeesData[Series],$M$8,MgmtFeesData[Options],$M$7,MgmtFeesData[SizeMin],"<="&M6,MgmtFeesData[SizeMax],">"&M6)

    Or use SUMPRODUCT with the same type of arguments (if you wanted a non-numeric value, you would need =INDEX(SUMPRODUCT(based on row number)) to return a string...)

    You need to decide what to do if your value exactly matches the break point size - I chose to go with the higher group, so you may want to change to
    < and >= instead of <= and >

    Just don't use either
    <= and >= or < and >
    Last edited by Bernie Deitrick; 10-15-2015 at 01:07 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    12-17-2013
    Location
    Greater NYC Area
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Lookup based on three criteria

    Perfect, thank you very much. I figured those less than / greater than signs would come in to play, but I just couldn't quite connect all the dots. I'll be sure to revise the value breaks.

    Thank you!

+ 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. [SOLVED] Lookup Based on 2 Criteria
    By Maroota in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-17-2012, 10:48 AM
  2. Lookup based off two criteria
    By h_aesa1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2011, 10:13 PM
  3. [SOLVED] Lookup based on 2 criteria
    By Barb R. in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 09-06-2005, 12:05 PM
  4. Lookup based on 2 criteria
    By Barb R. in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 09-06-2005, 07:05 AM
  5. Lookup based on 2 criteria
    By Barb R. in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 09-06-2005, 04:05 AM

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