+ Reply to Thread
Results 1 to 13 of 13

Lookup latest date in a table based on 2 criteria

  1. #1
    Registered User
    Join Date
    09-24-2014
    Location
    Pinjarra, Australia
    MS-Off Ver
    2011
    Posts
    27

    Lookup latest date in a table based on 2 criteria

    Good afternoon all,

    I am trying the following formula to return the maximum/latest date from a table based on 2 criterias:

    {=MAX(IF(AND('Completed Runs'!$F$4:$F$5000=R5,'Completed Runs'!$B$4:$B$5000="Outlook Plumbing"),'Completed Runs'!$H$4:$H$5000))}

    However, it returns 0

    Column B = Company Name
    Column F = Zones
    Column H = Dates of service
    R5 = Zone (criteria)

    So for this I would like to find what the last service date for company "Outlook Plumbing" in zone 1, was.

    Thank you.
    Marianne

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,785

    Re: Lookup latest date in a table based on 2 criteria

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Cheers!
    Deep Dave

    www.NeedForExcel.com

  3. #3
    Registered User
    Join Date
    09-24-2014
    Location
    Pinjarra, Australia
    MS-Off Ver
    2011
    Posts
    27

    Re: Lookup latest date in a table based on 2 criteria

    Thanks Dave.

    I have attached it. I think
    Attached Files Attached Files

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,785

    Re: Lookup latest date in a table based on 2 criteria

    Try this -

    =MAX((B22=$F$3:$F$18)*($B$3:$B$18="Outlook Plumbing")*$H$3:$H$18)

    ...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. Press F2 on that cell and try again.
    Last edited by NeedForExcel; 07-29-2015 at 01:04 AM.

  5. #5
    Registered User
    Join Date
    09-24-2014
    Location
    Pinjarra, Australia
    MS-Off Ver
    2011
    Posts
    27

    Re: Lookup latest date in a table based on 2 criteria

    Thanks Dave,

    It worked on my little mock sheet, but when I put it into the main file it returns a #VALUE

    {=MAX((R4='Completed Runs'!$F$4:$F$5000)*('Completed Runs'!$B$4:$B$5000="Outlook Plumbing")*$H$4:$H$5000)}

    And yes I have activated the array {}.

    Thanks for your help.

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,547

    Re: Lookup latest date in a table based on 2 criteria

    Try this array formula to find any text in column H.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,785

    Re: Lookup latest date in a table based on 2 criteria

    Quote Originally Posted by mariannehislop View Post
    It worked on my little mock sheet, but when I put it into the main file it returns a #VALUE.
    Probably something with references must have messed up, or the columns would have changed..

    Please check it out, and if you still have a problem, kindly attach the sheet with errors..

  8. #8
    Registered User
    Join Date
    09-24-2014
    Location
    Pinjarra, Australia
    MS-Off Ver
    2011
    Posts
    27

    Re: Lookup latest date in a table based on 2 criteria

    OK I worked out why it isn't working in the main sheet, it's before I have text in column "H". Is there a work around this?

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,785

    Re: Lookup latest date in a table based on 2 criteria

    But then if there is Text in Column H, how could you possibly find the Max of it?

    Or is it that Dates are entered as Text, in such a case, you will have to change the Text Dates to Actual dates..

  10. #10
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,547

    Re: Lookup latest date in a table based on 2 criteria

    Try this, it will just ignore the text
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-24-2014
    Location
    Pinjarra, Australia
    MS-Off Ver
    2011
    Posts
    27

    Re: Lookup latest date in a table based on 2 criteria

    Brilliant. Got it. Thanks Dave, The =match pulled up some dates that were entered incorrectly as text.

    Cheers

  12. #12
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,785

    Re: Lookup latest date in a table based on 2 criteria

    Glad I could help!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  13. #13
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,547

    Re: Lookup latest date in a table based on 2 criteria


    ''''''''''

+ 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. Dax formula to lookup latest value for given criteria
    By giorobert88 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-15-2014, 11:50 AM
  2. Pull Latest Date 2 Criteria Lookup
    By erock24 in forum Excel General
    Replies: 2
    Last Post: 08-25-2014, 09:47 PM
  3. Return latest date based on matching criteria
    By senthilvenkatesh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2014, 02:49 AM
  4. [SOLVED] Vlookup Base on Criteria but pick up the entry based on the latest date
    By kelkelkel08 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2014, 05:37 AM
  5. Powerpivot - Returning latest update based on latest date
    By Kehjz in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-22-2013, 02:45 PM
  6. Replies: 1
    Last Post: 01-20-2012, 09:44 AM
  7. Lookup Latest Date Using Multiple Criteria
    By maggioant in forum Excel General
    Replies: 2
    Last Post: 01-26-2010, 04: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