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:
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:
But this returns a #VALUE! error.
Once we can get that working, the lookup of the release date should then also include the Generation Lookup Value.
I have also attached the sample workbook for anyone who can help.
Any advise, or solutions would be very helpful! :)
Sample.xlsx
For the purpose of this Question I have simplified the data set, and attached a sample file.
The data set is as follows:
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]))}But this returns a #VALUE! error.
Once we can get that working, the lookup of the release date should then also include the Generation Lookup Value.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
then try
=IFERROR(MIN(IF((--(SampleData[Generation]=C4)*IFERROR(SEARCH(C3,SampleData[Description]),0)>0),IF(ISNUMBER(SampleData[Released]),SampleData[Released],NA()))),"")ASKER
Brilliant! Thank you Rgonzo1971. :)
ASKER