Link to home
Create AccountLog in
Avatar of Terry Rogers
Terry RogersFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel MAX IF with Conditions

I have a set of data that I need to do some conditional lookup's on.

For the purpose of this Question I have simplified the data set, and attached a sample file.

The data set is as follows:

User generated image
What I need to find is the lowest release date where the description contains a particular lookup value.

As an example I would like the earliest release date for the iPad Air.

The first issue I noticed is whatever solution used would potentially return a an incorrect date because of the various differencial model descriptions.

To get around this I added the Generation column to my dataset.

The first formula I need is to lookup the lowest value in the Generation Column where the Description CONTAINS the Lookup Value.

I started by trying this formula:


Open in new window

But this returns a #VALUE! error.

User generated image
Once we can get that working, the lookup of the release date should then also include the Generation Lookup Value.

User generated image
I have also attached the sample workbook for anyone who can help.

Any advise, or solutions would be very helpful! :)
Avatar of Rgonzo1971

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Terry Rogers


Thank you Rgonzo1971! This works perfectly. :)
Hi Rgonzo1971,

Apologies, I closed this prematurely.

The formula works, but not if there is no date in the released column.

See attached...

How would we alter the query to return "nothing" or an empty return?
Avatar of Rgonzo1971

then try

Open in new window

Brilliant! Thank you Rgonzo1971. :)