+ Reply to Thread
Results 1 to 3 of 3

Select Vehicle Code,last date in the month,oldest time on that date return Amount value

  1. #1
    Registered User
    Join Date
    11-08-2019
    Location
    Kampala
    MS-Off Ver
    Office 2007
    Posts
    9

    Select Vehicle Code,last date in the month,oldest time on that date return Amount value

    I have four columns as shown below
    Two vehicle codes 56035 and 78733
    create a formula that can return the amount consumed by each car on the oldest date in the month,with the oldest time on that very date.
    Attached Files Attached Files
    Last edited by munaaba; 11-09-2019 at 12:45 AM. Reason: need to attach the schedule

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Select Vehicle Code,last date in the month,oldest time on that date return Amount valu

    You should show some expected outcomes with this sort of question.

    See if this is what you want. I put the Card No in H2...
    H
    2
    56035
    3
    1576200

    H3=INDEX($E$3:$E$24,MATCH(MIN(IF($B$3:$B$24=H2,C3:C24)),$C$3:$C$24,0))
    ...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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Select Vehicle Code,last date in the month,oldest time on that date return Amount valu

    Say in G3:G4 you put your Card nums, in H3 put the following ARRAY formula (activate with CTRL+SHIFT+ENTER), then drag down to H4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also done in attached file.
    Attached Files Attached Files

+ 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: 4
    Last Post: 09-05-2019, 01:55 PM
  2. Replies: 16
    Last Post: 01-31-2019, 03:31 PM
  3. [SOLVED] Return the oldest date when cell does not contain an *(Asterix)
    By C J W in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2017, 12:25 PM
  4. Return oldest date in range when a file is open
    By mementomori in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-08-2016, 10:17 AM
  5. Return oldest date with some dates excluded
    By Stromming in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-30-2014, 06:32 AM
  6. return oldest date based on call value
    By 288enzo in forum Excel General
    Replies: 8
    Last Post: 02-28-2014, 10:50 PM
  7. Replies: 2
    Last Post: 03-22-2013, 02:20 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