+ Reply to Thread
Results 1 to 8 of 8

index match - 2 criteria (Date & String) one needs to be less than the other exact

  1. 08-04-2017, 06:22 AM #1
    timhuls1
    timhuls1 is offline
    Registered User
    Join Date
    08-04-2017
    Location
    ghent, belgium
    MS-Off Ver
    2016
    Posts
    3

    index match - 2 criteria (Date & String) one needs to be less than the other exact

    goodafternoon

    first post...

    I have 2 tables, "picked sales orders" and "produced goods". 1 lot can contain multiple pallets. I need to find the produced pallet in "picked sales orders" from the table "produced goods".
    So I have to find closet date less than the picked date in the table produced goods with exact match to the lot.

    In my formula it gets the date correct but not the exact lot.

    Dont quite know how to formulate it correctly in Excel... Any help would be much appreciated!

    Picture:
    Knipsel.JPG
    Example (wasnt able to upload attachment)
    https://we.tl/g3BfF6YX1K

    thank you!
    Attached Files Attached Files
    Last edited by timhuls1; 08-04-2017 at 06:27 AM. Reason: included example workbook
    Reply With QuoteRegister To Reply
  2. 08-04-2017, 06:25 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,225

    Re: index match - 2 criteria (Date & String) one needs to be less than the other exact

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Reply With QuoteRegister To Reply
  3. 08-04-2017, 06:25 AM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,069

    Re: index match - 2 criteria (Date & String) one needs to be less than the other exact

    Edit your post - click on Go Advanced - scroll down to Manage Attachments - attach your file.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 08-04-2017, 06:28 AM #4
    timhuls1
    timhuls1 is offline
    Registered User
    Join Date
    08-04-2017
    Location
    ghent, belgium
    MS-Off Ver
    2016
    Posts
    3

    Re: index match - 2 criteria (Date & String) one needs to be less than the other exact

    Thank you, I included the example workbook.
    Reply With QuoteRegister To Reply
  5. 08-04-2017, 06:31 AM #5
    samba_ravi
    samba_ravi is online now
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: index match - 2 criteria (Date & String) one needs to be less than the other exact

    C3=IFERROR(LOOKUP(2,1/(($E$3:$E$6<=$A3)*($F$3:$F$6=$B3)),$G$3:$G$6),"")
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  6. 08-04-2017, 06:38 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: index match - 2 criteria (Date & String) one needs to be less than the other exact

    or

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


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  7. 08-04-2017, 08:22 AM #7
    timhuls1
    timhuls1 is offline
    Registered User
    Join Date
    08-04-2017
    Location
    ghent, belgium
    MS-Off Ver
    2016
    Posts
    3

    Re: index match - 2 criteria (Date & String) one needs to be less than the other exact

    Thank you this works!
    Reply With QuoteRegister To Reply
  8. 08-04-2017, 08:34 AM #8
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,069

    Re: index match - 2 criteria (Date & String) one needs to be less than the other exact

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index Match with Multiple Criteria (exact for text and greater than value for number)
    By mxlew in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-26-2021, 05:36 AM
  2. [SOLVED] Index(Match with 2 criteria; 1 exact and 1 approx
    By JuJuBe in forum Excel General
    Replies: 2
    Last Post: 06-09-2016, 04:27 PM
  3. Formula needed for partial string match as part of index match with multiple criteria
    By Marvelous in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  4. [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
  5. [SOLVED] INDEX date that MATCH on date range, MATCH code(string)
    By Dahlia in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2014, 03:33 AM
  6. 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
  7. Index Match nesting w/ partial text string criteria
    By dohearn in forum Excel General
    Replies: 1
    Last Post: 10-25-2011, 03:42 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1