+ Reply to Thread
Results 1 to 6 of 6

Look a a date in range of dates in column A, then say which date has lower value in B

  1. #1
    Registered User
    Join Date
    03-28-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Look a a date in range of dates in column A, then say which date has lower value in B

    Hi,
    I'm trying to find how can I look a date..... in range of dates in column A, then say which date has lower value in B.
    Thanks



    Reference of date in L3 to look in column A --> 10/27/2014

    Criteria:
    1) Identify how many dates in column A are EQUAL or GREATER to value in L3 (see in yellow)
    2) Then, from the dates found in column A, say which has the LOWER value in column B (see in yellow)


    The result of criteria 1) and 2) has to show the number of the ROW in L10 --> 9



    COLUMN A COLUMN
    10/14/2014 10
    10/15/2014 11
    10/15/2014 12
    10/15/2014 13
    10/16/2014 14
    10/17/2014 15
    10/17/2014 16
    10/21/2014 17
    10/28/2014 18
    10/27/2014 19
    10/22/2014 20
    10/23/2014 21
    10/27/2014 22
    10/24/2014 23
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,499

    Re: Look a a date in range of dates in column A, then say which date has lower value in B

    Perhaps the Array Formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    committed with Ctrl-Shift-Enter rather than just Enter.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


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

    Re: Look a a date in range of dates in column A, then say which date has lower value in B

    This is also an arrayed formula. It assumes that there are no duplicate values in column B

    =MATCH(MIN(IF($A$1:$A$14 >= $L$3,$B$1:$B$14)),$B$1:$B$14,0)
    or more simply
    =MIN(IF($A$1:$A$14 >= $L$3,ROW($B$1:$B$14)))
    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

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Look a a date in range of dates in column A, then say which date has lower value in B

    Hi,

    three independent formulae


    Please Login or Register  to view this content.

    Min per Dates >=L3

    Please Login or Register  to view this content.
    Position of MIN per Dates >=L3

    Please Login or Register  to view this content.

    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  5. #5
    Registered User
    Join Date
    03-28-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Look a a date in range of dates in column A, then say which date has lower value in B

    Thanks to everyone of you guys! you are the best! all solutions works perfectly!!!!!!!
    This is now SOLVED!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,499

    Re: Look a a date in range of dates in column A, then say which date has lower value in B

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. Replies: 2
    Last Post: 06-07-2013, 02:21 PM
  2. Replies: 0
    Last Post: 05-30-2013, 07:50 PM
  3. [SOLVED] Date Filter based on Cell Input - single date or range of dates
    By babaloo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2012, 10:32 AM
  4. Replies: 3
    Last Post: 08-11-2009, 11:46 AM
  5. Replies: 25
    Last Post: 09-07-2005, 12:05 AM

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