+ Reply to Thread
Results 1 to 4 of 4

Lookup Value Based on Multiple Criteria (one of which, not exact)

  1. #1
    Forum Contributor
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    MS Office: Excel 365
    Posts
    163

    Lookup Value Based on Multiple Criteria (one of which, not exact)

    Hi --

    Some of my clients have a tiered commission rate structure depending on spend amounts. I'm hoping to house all the commission rate structures in one table, and return the appropraite commission rate based off multiple criteria (one of which being a number within a range).

    I attached an excel file that get's me what I need if I was only looking at one crieteria (whether the designated spend amount is within a range -- ish, you'll see) -- formula in E4.
    However, I'd like to base this formula off columns B & C as well, since i plan on inputting multiple clients/projects into one table.

    Is this possible?

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Lookup Value Based on Multiple Criteria (one of which, not exact)

    Try the formula below in E4. It should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

    =IFERROR(INDEX(COMM_TABLE[% of Media],MATCH(1,(COMM_TABLE[Client]=B4)*(COMM_TABLE[Project Code]=C4)*(COMM_TABLE[Min Spend]<=D4)*(COMM_TABLE[Max Spend]>=D4),0)),"No Match")

    It should return the match that fits the choices in B4:D4.


    EDIT: Here's an alternative that addresses the "And Up" text a bit more explicitly. It should also be array-entered.

    =IFERROR(INDEX(COMM_TABLE[% of Media],MATCH(1,(COMM_TABLE[Client]=B4)*(COMM_TABLE[Project Code]=C4)*(COMM_TABLE[Min Spend]<=D4)*((COMM_TABLE[Max Spend]>=D4)+(NOT(ISNUMBER(COMM_TABLE[Max Spend])))>0),0)),"No Match")
    Last edited by CAntosh; 11-16-2017 at 05:30 PM.
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Forum Contributor
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    MS Office: Excel 365
    Posts
    163

    Re: Lookup Value Based on Multiple Criteria (one of which, not exact)

    Quote Originally Posted by CAntosh View Post
    Try the formula below in E4. It should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

    =IFERROR(INDEX(COMM_TABLE[% of Media],MATCH(1,(COMM_TABLE[Client]=B4)*(COMM_TABLE[Project Code]=C4)*(COMM_TABLE[Min Spend]<=D4)*(COMM_TABLE[Max Spend]>=D4),0)),"No Match")

    It should return the match that fits the choices in B4:D4.


    EDIT: Here's an alternative that addresses the "And Up" text a bit more explicitly. It should also be array-entered.

    =IFERROR(INDEX(COMM_TABLE[% of Media],MATCH(1,(COMM_TABLE[Client]=B4)*(COMM_TABLE[Project Code]=C4)*(COMM_TABLE[Min Spend]<=D4)*((COMM_TABLE[Max Spend]>=D4)+(NOT(ISNUMBER(COMM_TABLE[Max Spend])))>0),0)),"No Match")
    ahhh! this is beautiful
    thank you!!!

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Lookup Value Based on Multiple Criteria (one of which, not exact)

    Glad to help, good luck!

+ 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 2 criteria exact and in a range
    By geebers97 in forum Excel General
    Replies: 10
    Last Post: 06-24-2015, 09:34 AM
  2. Replies: 9
    Last Post: 08-20-2014, 04:43 PM
  3. [SOLVED] Lookup value based on multiple criteria
    By Kuehl5000 in forum Excel General
    Replies: 3
    Last Post: 06-11-2014, 09:14 AM
  4. [SOLVED] Lookup and sum multiple values based on a single criteria in multiple sheets.
    By paulsanett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 12:18 PM
  5. [SOLVED] Multiple (Some Exact/ Some Not) Criteria Lookup
    By stats90 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-07-2013, 05:02 PM
  6. LOOKUP Having one Exact matching criteria and one between 2 date ranges.
    By Grahame Hamilton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-24-2012, 06:28 AM
  7. Two criteria lookup - exact & closest match
    By kapucino in forum Excel General
    Replies: 3
    Last Post: 01-28-2012, 01:19 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