+ Reply to Thread
Results 1 to 8 of 8

Vlookup formula to return max/min dates based on 1 criteria?

  1. #1
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Vlookup formula to return max/min dates based on 1 criteria?

    I need help with finding a max/min date within a VLOOKUP. I have fried my brain
    Overview: When working with students, I need to know specific information about their particular class, defined as “CRN” number.

    The Master Sheet acts as my database for all the college classes this semester. The Fee Page is where I can type in the CRN into the yellow section and the remaining fields populate with the corresponding data from the Master Sheet.

    Each row is 1 record in the Fee Page that shows the info I need for each college class. The CRN 10596 (criteria) shows Start Date of 9-Dec-15 and an End Date OF 9-Dec-15.

    The existing formula for Start Date P2 is =IFERROR(VLOOKUP(TEXT($A2,"0"),Master!$1:$10001,COLUMN(),FALSE),IFERROR(VLOOKUP($A2,Master!$1:$10001,COLUMN(),FALSE),""))

    The actual dates of the class are 19-Aug-15 through 9-Dec-15. I highlighted the cells that contain CRN 10596 start and end dates in the master sheet in blue. I have used the MAX and MIN function before but I can’t the formula to work. I am including the workbook for reference. Any ideas?
    Thanks so much for your assistance!
    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 2007,2010,365
    Posts
    44,447

    Re: Vlookup formula to return max/min dates based on 1 criteria?

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


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


    Both formulae need to be Array Entered with Ctrl-Shift-Enter rather than just Enter.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: Vlookup formula to return max/min dates based on 1 criteria?

    An array--well done sir! That's genius! This works perfectly, many thanks.

    Since I have copied the array into the entire columns, can I wrap the array with an IFFERROR so the blank cells stay blank? Thanks again--outstanding work!!!

    Hope

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Vlookup formula to return max/min dates based on 1 criteria?

    You're welcome. Thanks for the rep.

    Sure you can use IFERROR just don't forget to array enter the amended formula


    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.

  5. #5
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: Vlookup formula to return max/min dates based on 1 criteria?

    Hello again! The array works great for returning dates. In the rows that are blank I get "0-Jan-00" in Columns P & Q. I wrapped the formula with the IFERROR like this:

    =IFERROR(MIN(IF(Master!$A:$A=FeePage!A5&"",Master!$P:$P)),"")

    once I hit ctrl+shift+enter for the array, nothing happens. Thanks for your thoughts!

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

    Re: Vlookup formula to return max/min dates based on 1 criteria?

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



    Regards, TMS

  7. #7
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: Vlookup formula to return max/min dates based on 1 criteria?

    I clearly have so much to learn. Thank you again for your quick response and flawless solution! I use this sheet to help my veteran students use their educational benefits and I am so grateful for your genius!

    Cheers to you

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Vlookup formula to return max/min dates based on 1 criteria?

    You're welcome ... and thank you. We all have lots to learn, just depends on the topic(s) ;}

    Regards, TMS

+ 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] Formula to return value based on two criteria
    By marks9172 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2015, 02:52 PM
  2. Help with a formula to look up/return based on 2 criteria
    By Choppo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 09:48 PM
  3. [SOLVED] Formula to return specified value based on criteria
    By adriam25 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-23-2014, 12:39 AM
  4. Formula to return specified value based on criteria
    By adriam25 in forum Excel General
    Replies: 8
    Last Post: 09-07-2014, 06:21 AM
  5. [SOLVED] Formula to Return ID based on Multiple Criteria
    By boldcode in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 02:54 PM
  6. vlookup formula with two criteria that must be met to return a value
    By MBROOKS in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-12-2012, 07:04 PM
  7. Vlookup or other formula to return data given criteria
    By haduken in forum Excel General
    Replies: 14
    Last Post: 02-13-2011, 04:06 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