+ Reply to Thread
Results 1 to 14 of 14

Index and Match with Two Criteria, and date Match Type is Less Than

  1. #1
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Index and Match with Two Criteria, and date Match Type is Less Than

    Hello Experts,
    The current array formula has two criteria. The first part matches the "100" ID. The second part matches the "May 21, 2013" as an exact match...resulting in "#N/A" because "100" and "May 21, 2013" does not exist.

    How would you improve this array formula to add the Match Type of "1" Less Than in the second part? The "May 21, 2013" through to "June 19, 2013" needs to be converted to "May 20, 2013"...answer is cell B5.

    Shift+Ctrl+Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    [A] [B]
    ID# Bill Date [Row 1]
    201 10-Apr-13 [Row 2]
    100 20-Apr-13 [Row 3]
    201 10-May-13 [Row 4]
    100 20-May-13 [Row 5]
    201 10-Jun-13 [Row 6]
    100 20-Jun-13 [Row 7]

    I've tried Sumproduct also but it's not right either. It only works up to the next date "June 10, 2013" and errors with a "0" because the ID# is "201" (no longer "100"). I need to match to "100" and if the Match date is between "May 21, 2013" to "June 19, 2013", the answer should be "May 20, 2013".
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thanks in advance,
    Ricky

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Try this array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Thank-you ChemistB!
    Works like a charm. This is perfect!

    Greatly appreciate it.

    Regards,
    Ricky

    Quote Originally Posted by ChemistB View Post
    Try this array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does that work for you?

  4. #4
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Hi again Experts,
    Could we add a third criteria to this array formula (credit goes to ChemistB)? Currently, this formula has two criteria.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Column [F] is the new search criteria for "Yes".
    C1 searches for ID#
    D1 searches for Bill Date
    E1 searches for Qualifier (“Pending” or “No” or “Yes”)

    [A]...[B]............[F]
    ID#...Bill Date.....Qualifier [Row 1]
    201...10-Apr-13..Pending [Row 2]
    100...20-Apr-13...No [Row 3]
    201...10-May-13..Pending [Row 4]
    100...20-May-13..No [Row 5]
    100...20-May-13..Yes [Row 6]
    201...10-Jun-13...Pending [Row 7]
    100...20-Jun-13...No [Row 8]
    201...10-Jul-13....Pending [Row 9]
    100...20-Jul-13....Yes [Row 10]

    Test results would look like this:
    1) Search for ID# [100], Search for Bill Date [May 19, 2013 or any earlier date], Search for Qualifier [Yes] = #N/A error because no "Yes" qualifier exists before "May 20, 2013"
    2) Search for ID# [100], Search for Bill Date [May 20, 2013 to July 19, 2013 inclusive], Search for Qualifier [Yes] = “May 20, 2013” cell B6
    3) Search for ID# [100], Search for Bill Date [July 20, 2013 or any later date], Search for Qualifier [Yes] = “July 20, 2013” cell B10

    I am continuing this thread to retain the history; rather than, starting a new thread. My apologies, if this isn't the proper protocol. Your help is greatly appreciate it.

    Thanks in advance,
    Ricky

  5. #5
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    G'day Ricky,

    Uploading a small example of non sensitive information, with a before and after and this may fast track an answer.

  6. #6
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Hi ratcat,
    Here's the worksheet. Please advise.

    Regards,
    Ricky

    Quote Originally Posted by ratcat View Post
    G'day Ricky,

    Uploading a small example of non sensitive information, with a before and after and this may fast track an answer.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Bump. Please help, Experts.

    I've attached the sample spreadsheet. Please help revise the current Index and Match (with Match Type of Less Than) formula to add a third criteria..."Yes" based on the new "Qualifier" column.

    Thanks in advance,
    Ricky

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Try arrayed
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Thank-you so much ChemistB,
    You've come to my rescue yet again. This works nicely!

    I appreciate it very much.

    Regards,
    Ricky

    Quote Originally Posted by ChemistB View Post
    Try arrayed
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Hi ChemistB,
    I attempted to apply this to my workbook but unfortunately, my data is unsorted. I've searched for the workaround and found that the possible solution involves ABS and MIN. I'm unsure how to approach this. May I ask for your assistance again?

    Regards,
    Ricky

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    That formula is not dependent on anything being in a particular order. There must be other issues with your worksheet. Try using "Evaluate Formula" which is found on Formulas Tab (probably using a small subset of your data).

  12. #12
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Hi ChemistB;
    My real data has the dates are either in alphabetical order or from New to Old. Unfortunately, the dates and data can not be resorted. I've attached the sample spreadsheet.

    There are 3 sheets:
    Unsorted (formula works well)
    Date (Old to New) formula works well
    Date (New to Old) formula is giving the incorrect result compared to the other two sheets.

    Could you please have a look? I'd appreciate it very much.

    Regards,
    Ricky

    Quote Originally Posted by ChemistB View Post
    That formula is not dependent on anything being in a particular order. There must be other issues with your worksheet. Try using "Evaluate Formula" which is found on Formulas Tab (probably using a small subset of your data).
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    With the match function, that final argument of 1 indicates that the values are listed from smallest to largest and find the one equal to or less than
    If it's 0, it looks for an exact match and if it's -1, it's from largest to smallest. If your values are from largest to smallest, change it to arrayed

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Neither formula will be reliable for unsorted data. I was wrong (gasp).

  14. #14
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Re: Index and Match with Two Criteria, and date Match Type is Less Than

    Hi ChemistB,
    If the data is unsorted, neither from smallest to large or vice versa, is there another more reliable function better than Match?

    Regards,
    Ricky

+ 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 with date range criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 10-24-2013, 04:58 PM
  2. Index / match 2 criteria row and column containing date - please help!!
    By willgt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2013, 08:03 PM
  3. [SOLVED] Index match formula to search for two criteria (date & name)
    By davechamp1983 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2012, 10:00 PM
  4. Index Match based on 2 Criteria only returning 1 match
    By stsanders22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 11:26 PM
  5. Replies: 3
    Last Post: 08-17-2010, 02:54 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