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:
![Simplified Data Set]()
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.
![Lookup Fields]()
Once we can get that working, the lookup of the release date should then also include the Generation Lookup Value.
![Spreadsheet Screenshot]()
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
Microsoft ExcelMicrosoft Office
Last Comment
Terry Rogers
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you Rgonzo1971! This works perfectly. :)
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. :)