# Lookup latest date in a table based on 2 criteria

1. ## 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. ## 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

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

Thanks Dave.

I have attached it. I think

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

5. ## 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 {}.

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

Try this array formula to find any text in column H.
Formula:
`Please Login or Register  to view this content.`

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

Originally Posted by mariannehislop
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. ## 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. ## 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. ## Re: Lookup latest date in a table based on 2 criteria

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

11. ## 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

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

''''''''''

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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