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:

{=MIN(IF(SEARCH(C3,SampleData[Description])>0,SampleData[Generation]))}

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! :)
Sample.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

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

ASKER

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?
2.xlsx
Avatar of Rgonzo1971
Rgonzo1971

then try
=IFERROR(MIN(IF((--(SampleData[Generation]=C4)*IFERROR(SEARCH(C3,SampleData[Description]),0)>0),IF(ISNUMBER(SampleData[Released]),SampleData[Released],NA()))),"")

Open in new window

Brilliant! Thank you Rgonzo1971. :)