+ Reply to Thread
Results 1 to 5 of 5

Index Match equal to or less than date with multiple criteria

  1. #1
    Registered User
    Join Date
    04-16-2014
    Location
    Southern USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Index Match equal to or less than date with multiple criteria

    Hello everyone,

    This is my first post. Hope someone can help.

    I have created an Excel spreadsheet with a data table containing the well number, test date and production rate. I want to use the Index Match function to look up the production rate based on the day of the month. I have created the equations, but the only problem is the lookup returns the closest test date. I need it to return the closest date equal to or less than the day of the month (i.e., can not use test before it has been performed).

    I am using the following equation to obtain the test date:

    =INDEX(Sheet1!$B$3:$B$20,MATCH(MIN(ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307))),ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307)),0))

    I have attached a copy of the spreadsheet for reference.

    Any help would be greatly appreciated.

    harrismlzn
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-16-2014
    Location
    Southern USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Index Match equal to or less than date with multiple criteria

    I was able to come up with a fix. The equation is very long, but it gets the job done. Basically, I utilized the IF statement with the INDEX MATCH to move up to the previous test when the selected test date is later than the date of interest.

    Below is the equation:

    =IF(INDEX(Sheet1!$B$3:$B$20,MATCH(MIN(ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307))),ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307)),0))>F3,INDEX(Sheet1!$B$3:$B$20,MATCH(MIN(ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307))),ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307)),0)-1),INDEX(Sheet1!$B$3:$B$20,MATCH(MIN(ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307))),ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307)),0)))

    I have also attached a copy of the spreadsheet for reference.

    Thanks,
    harrismlzn
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Index Match equal to or less than date with multiple criteria

    Here is an array formula solution.

    Surprisingly similar to this:
    http://www.excelforum.com/excel-form...-criteria.html
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-16-2014
    Location
    Southern USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Index Match equal to or less than date with multiple criteria

    Jacc,

    Thanks for a more simplified version!

    harrismlzn

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Index Match equal to or less than date with multiple criteria


    --------

+ 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] Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date
    By jcox1953 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2014, 02:41 PM
  2. [SOLVED] Index match multiple criteria based on earliest date
    By dchubbock in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-28-2013, 07:18 PM
  3. Index and Match with Two Criteria, and date Match Type is Less Than
    By ExcelQuestion in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-11-2013, 08:57 AM
  4. [SOLVED] Using Index/Match (Equal or less than) with Multiple Criteria
    By Padal in forum Excel General
    Replies: 4
    Last Post: 07-06-2012, 05:10 PM
  5. Replies: 3
    Last Post: 08-17-2010, 02:54 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