Results 1 to 3 of 3

Index/Match Function for Finding Result in between Two Values with Two Criteria

Threaded View

  1. #1
    Registered User
    Join Date
    05-02-2014
    Location
    London, ON
    MS-Off Ver
    Excel 2010
    Posts
    2

    Index/Match Function for Finding Result in between Two Values with Two Criteria

    Tried to run a large function but ran into 64 max nested IF statements. I read that i needed to make a table and run an Index/Match type formula. I could figure out how to run with one min/max but couldn't figure out how to run with two.

    What I'm looking for is if Value1 (Type1) falls between MinType1 and MaxType1 and Value2 (Type2) falls between MinType2 and MaxType2, it will give you the result (Amount) from that line. Ex Data is provided below the chart.

    Chart:
    MinType1 MaxType2 MinType2 Max Type2 Amount
    100 200 1000 2000 $100
    100 200 2001 3000 $200
    201 300 1000 2000 $300
    201 300 2001 3000 $400

    Data:
    Type1: 150
    Type2: 1500
    Result: $100

    Might be easier to read in attachment.
    Attached Files Attached Files
    Last edited by KyleElliott; 06-08-2017 at 10:53 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 08-11-2016, 02:18 AM
  2. [SOLVED] Index/Match Multiple Criteria only Finds First Result
    By DomSza in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2015, 08:57 AM
  3. Index-Match function not finding matches with newly pasted values
    By murp5972 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-04-2015, 03:44 PM
  4. [SOLVED] Multiple criteria for Index and Match with if(and( & if(or( result #value?
    By v.rodgers in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2015, 12:52 AM
  5. [SOLVED] index and match function result from three way look up
    By johnodys in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-15-2013, 03:32 AM
  6. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 PM
  7. Replies: 7
    Last Post: 06-09-2011, 05:25 PM

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