+ Reply to Thread
Results 1 to 9 of 9

Index Match with Multiple Criteria One to Nearest Date

  1. #1
    Registered User
    Join Date
    06-13-2014
    Posts
    4

    Index Match with Multiple Criteria One to Nearest Date

    UnitCostSampleDate.xlsx

    I have tried many different Index Match combinations and I am not able to accurately achieve what I need. I need to pull UnitCost from Table2 into Table1 where Table1.Description2=Table2.PartNum AND Table1.Date(ColumnName)=Table2.OrderDate. The problem I am running into is the dates are not exact. I need it to go to the nearest value with still maintaining the criteria of Table1.Description2=Table2.PartNum. Concatenating the two columns does not work as it looses the ability to find nearest date.


    Attached is sample date out of the two workbooks I am using.

    Just a few trials
    =INDEX(Table2$D$2:$D$65153,MATCH(C3,Table2$A$2:$A$65153, 0)) - Only one criteria, so it does not provide accurate results.

    =INDEX(Table2$C$2:$C$65153,MATCH(AND(Table2$A$2:$A$65153=C6),("06/01/2012"<=[Table2][OrderDate]))

    =IF(COUNTIF(Table2[PartNum],C5)=0,"-",MIN(IF(IF(IFERROR(VLOOKUP(C5,Table2[PartNum],1,FALSE)=C5, 0), ABS(Table2[OrderDate]-"05/03/2012"))=MIN(IF(IFERROR(Table2[PartNum],0)=C5,ABS(Table2[OrderDate]-"05/03/2012"))),Table2[OrderDate])))

    =MIN(IF(IF(IFERROR(VLOOKUP(C5,Table2[PartNum],1,FALSE)=C5,0),ABS(Table2[OrderDate]-"05/03/2012"))=MIN(IF(IFERROR(VLOOKUP(C5,Table2[PartNum],1,FALSE)=C5,0),ABS(Table2[OrderDate]-"05/03/2012"))),Table2[OrderDate]))


    Kind Regards,

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Index Match with Multiple Criteria One to Nearest Date

    Hi, and welcome to the forum.

    These things are always easier to answer if we can see the workbook in question. Please upload.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-13-2014
    Posts
    4

    Re: Index Match with Multiple Criteria One to Nearest Date

    SalesData.xlsxCostData.xlsx

    Thanks for the welcome and quick response. I have attached a non-tabled version of the two files. The size limitation made it so I was not able to upload the original versions. Let me know if this helps.

    Kind Regards,

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

    Re: Index Match with Multiple Criteria One to Nearest Date

    I made a formula in your first workbook to start with. See if this is something you can use. I haven't done much testing so I leave that up to you.


    Edit: I forgot to mention, the dates in the column headers are a mess, different formats and one has a figure too much. It may very well be that the formula I provided does not work with the date format in that column. It may depend on your local setting so I'm not sure.
    Attached Files Attached Files
    Last edited by Jacc; 06-14-2014 at 04:11 PM.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Index Match with Multiple Criteria One to Nearest Date

    in your table 2 you use a value 201311.

    you can also use a formula for that e.g.

    =year($b21)

    =month($b21)

    =day($b21)
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    06-13-2014
    Posts
    4

    Re: Index Match with Multiple Criteria One to Nearest Date

    I have been trying this formula off and on over the last couple of days, but still not able to get it to function correctly. Description2=PartNum is not working correctly. It is comparing Description2 and PartNum of the same row in each workbook.

    Example:
    Description2 (C4) = 3M.... the formula is trying to equal it to
    PartNum (A4) = AME..... Each are from row 4 which is always causing a false value.


    NOTE: The dates in my workbooks are showing correctly. When I copied them over to the new workbook, it looks like it added a number to the end of each date that was duplicated.


    Kind Regards,

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

    Re: Index Match with Multiple Criteria One to Nearest Date

    I forgot to mention, this is an array formula. You have to enter it with Ctrl + Shift + Enter, not just Enter. Sorry for any inconvinience that may have caused you.

  8. #8
    Registered User
    Join Date
    06-13-2014
    Posts
    4

    Re: Index Match with Multiple Criteria One to Nearest Date

    Lol, this is one of those situations I feel like I should hit my head against a wall for not thinking to try that. I am not sure why my mind did not go there, but it did not.

    Anyways, thanks for the assistance on this. As soon as I put it into the array it appears to be working correctly. So far everything I have check is returning the appropriate value.

    Kind Regards,

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

    Re: Index Match with Multiple Criteria One to Nearest Date

    I (and everyone else with some experience on the forum) always write that sentence about Ctrl + Shift + Enter everytime an array formula is posted, not knowing if this is old news to the original poster or not. I think I have forgotten it one or two times during my time here, you were very unlucky indeed.
    On the other hand, now you will never forget.

+ 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 equal to or less than date with multiple criteria
    By harrismlzn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2014, 05:38 PM
  2. [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
  3. [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
  4. 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
  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