Formula - Combine exclusions in a search

[ChrisMac1]

Hi there... I have a formula that I believe is currently returning results from blank cells. Any idea how I get those ignored too in the following formula please?

=MIN(IF(ORGANIC!$AG$2:$AG$100000<>"RESOLVED", ORGANIC!$S$2:$S$100000))

The result needed is the item in column AG (rows 2 to 100000) that has the oldest date shown in column S (rows 2 to 100000), and items called RESOLVED should not be included.

Many thanks,
MAC

 

[Fluff]

How about

=TAKE(SORT(FILTER(ORGANIC!$S$2:$AG$100000,(ORGANIC!$AG$2:$AG$100000<>"RESOLVED")*(ORGANIC!$S$2:$S$100000<>"")),1),1,-1)

 

[ChrisMac1]

WOW, I would never have got anywhere near there

It almost works except it's returning the item name and not the date. Sorry if I have written something misleading. The item is in column AG and the date is in column S, and I am not confident trying to tweak your formula, sorry, can you help me again please? It's the date IO need for that oldest item not called blank or RESOLVED

 

[Fluff]

How about

=TAKE(SORT(FILTER(ORGANIC!$S$2:$S$100000,(ORGANIC!$AG$2:$AG$100000<>"RESOLVED")*(ORGANIC!$S$2:$S$100000<>""))),1)

 

[ChrisMac1]

Ahhhhhhhhh lovely, that's got it. Thanks so much Fluff!

 

[RajeshKumar1313]

Hi there... I have a formula that I believe is currently returning results from blank cells. Any idea how I get those ignored too in the following formula please?

=MIN(IF(ORGANIC!$AG$2:$AG$100000<>"RESOLVED", ORGANIC!$S$2:$S$100000))

The result needed is the item in column AG (rows 2 to 100000) that has the oldest date shown in column S (rows 2 to 100000), and items called RESOLVED should not be included.

Many thanks,
MAC

try this
=MIN(IF(ORGANIC!$AG$2:$AG$100000<>"RESOLVED",IF(ISBLANK(ORGANIC!$S$2:$S$100000),"",ORGANIC!$S$2:$S$100000),""))

 

[ChrisMac1]

...And sorry for the direct message, just ignore it.

 

[ChrisMac1]

try this
=MIN(IF(ORGANIC!$AG$2:$AG$100000<>"RESOLVED",IF(ISBLANK(ORGANIC!$S$2:$S$100000),"",ORGANIC!$S$2:$S$100000),""))

Thanks Rajesh, Fluff's answer worked, but cheers for responding.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ChrisMac1]

You're welcome & thanks for the feedback.

Fluff, very sorry, this was my fault, an oversight on the formatting. How can I include this formatting that I have on another formula above? =UPPER(TEXT(ORGANIC!$S$2, "d MMM 'yy"))

It changes the date to UPPERCASE and a specific format.