+ Reply to Thread
Results 1 to 4 of 4

Return value based on earliest Date and Criteria

  1. #1
    Registered User
    Join Date
    05-06-2020
    Location
    Boston MA
    MS-Off Ver
    2019
    Posts
    2

    Return value based on earliest Date and Criteria

    Hello,

    I realize there may be a similar question out there but had trouble finding exactly what I was looking for so I hope someone here can help!

    I am looking to return the earliest stage based off of certain criteria. In the data, I have Account data for different accounts (Column C). In Column E I have the initial Stage Date, which is the first time the Stage was set for that account.

    What I'm looking for: I'm looking for the first Stage that was set on the account. For example, "Dog" was a Customer -Lost stage and I want to show that on Column F in F5 and F6. I am able to gather the initial stage date and would like to use the initial stage date and account name to inform me what the initial stage was of the account.

    I hope that helps. I have attached example data here.

    Thanks so much!
    Attached Files Attached Files

  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,929

    Re: Return value based on earliest Date and Criteria

    Try this ARRAY formula in F2...
    =INDEX($D$2:$D$13,MATCH(MIN(IF($C$2:$C$13=C2,$E$2:$E$13)),$E$2:$E$13,0))
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    Then copy down as needed.

    Note that if there are 2 identical date/times, this may cause an error. ATM your dates also have a time attached so that will increase the accuracy and reduce the likelihood of errors
    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
    Registered User
    Join Date
    05-06-2020
    Location
    Boston MA
    MS-Off Ver
    2019
    Posts
    2

    Re: Return value based on earliest Date and Criteria

    This is great and worked! Thank you so much. I will mark this as solved!

  4. #4
    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,929

    Re: Return value based on earliest Date and Criteria

    Happy to help and thanks for the feedback

+ 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. Find earliest date before today based on criteria
    By DanNielsen in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-28-2018, 10:47 AM
  2. [SOLVED] Count Unique Values Based on Earliest Date Criteria
    By Aquamore in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2014, 09:12 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. Replies: 11
    Last Post: 04-08-2013, 05:53 PM
  5. [SOLVED] formula to look up value and return the earliest date?
    By ea223 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2013, 08:27 PM
  6. Find earliest date based on specific criteria in other columns
    By cat2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2011, 11:50 AM
  7. Find earliest date based on certain criteria
    By SeanKosmann in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 01-26-2010, 11:32 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