+ Reply to Thread
Results 1 to 4 of 4

How To lookup a calender date Between Two Dates And Return Corresponding Value

  1. #1
    Registered User
    Join Date
    10-06-2017
    Location
    MUMBAI
    MS-Off Ver
    1705
    Posts
    3

    How To lookup a calender date Between Two Dates And Return Corresponding Value

    I have a report of some Vehicle schedule, now, i want to get the specific Location for a given vehicle number of a given Date which is between two dates or equal to two dates amongst column B & C. I would like to look up for vehicle MH15EG4910 for calemder dates as shown in row H2:AK2 , if this date falls between the start trip date Column B and Trip End Date Column C within the report, then return its corresponding Trip Concatenating Start-End as shown Cell I4 Location name. Please help to sort out.

    Output required as is available in cells I4 to L4, N4 to V4, Z4 to AC4, AD4 to AF4.

    Data Source availabe in Table Range A2:E24.

    I want Output for Vehicle Number Mentioned in Cell G4.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: How To lookup a calender date Between Two Dates And Return Corresponding Value

    in H4
    =IFERROR(INDEX($D$3:$D$24,MATCH($G4,IF((H$2>=$B$3:$B$24)*(H$2<=$C$3:$C$24),$A$3:$A$24),0),1)&"-"&INDEX($E$3:$E$24,MATCH($G4,IF((H$2>=$B$3:$B$24)*(H$2<=$C$3:$C$24),$A$3:$A$24),0),1),"")
    Array formula, use Ctrl-Shift-Enter
    copy across the columns
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: How To lookup a calender date Between Two Dates And Return Corresponding Value

    Aanother way ...

    =IFERROR(LOOKUP(2,1/($A$3:$A$24=$G$4)/($B$3:$B$24<=H2)/($C$3:$C$24>=H2),$D$3:$D$24&"-"&$E$3:$E$24),"")

    Normal Enter.

  4. #4
    Registered User
    Join Date
    10-06-2017
    Location
    MUMBAI
    MS-Off Ver
    1705
    Posts
    3

    Re: How To lookup a calender date Between Two Dates And Return Corresponding Value

    I am very thankful to you both @Special-K & @Phuocam. Thanks for this immediate help. Let me know, if i can contribute anything, anytime to you guys. Thank A Lot !! :-) :-) Cheers !!!

+ 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] Nest lookup Function to lookup name between date range and return value
    By bbeards in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2018, 01:37 PM
  2. [SOLVED] Booking Calender - Lookup multiple bookings across date ranges for
    By Smoestar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-12-2017, 07:38 AM
  3. [SOLVED] Lookup dates in cell range and return latest date and name
    By Glorioso999 in forum Excel General
    Replies: 6
    Last Post: 01-28-2017, 12:43 PM
  4. Replies: 2
    Last Post: 06-07-2013, 02:21 PM
  5. Converting 2010 Julian dates to calender date
    By krie2509 in forum Excel General
    Replies: 8
    Last Post: 02-03-2010, 06:36 PM
  6. Conditional Format Dates in a Calender when Matches dates in a list
    By Lungfish in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2009, 06:23 AM
  7. Replies: 2
    Last Post: 08-12-2007, 12:24 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