I need to incorporate the following array with an "iserror".

{=MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes",Today!$C2:$C$15000)))}Thank you.

Hi, What is generating the error.

Is there an error value in one of the source cells in columns C, D or O.

Or is the error generated by the formula itself - unlikely because MIN is tolerant of empty cells and non-numeric values.

If your array formula returns an error what are you hoping to display instead.

Regards

If the "CANF" is not there (which can be the case, the MIN shows 01/01/1900. Thank you

Hi, The formula you posted isn't returning an error, it's just not returning what you wanted !

When "CANF" is not present in the range in column D, the MIN function is, I think, returning zero.

I am assuming that the cell that contains this formula is formatted as a date.

You show the result as 01/01/1900 - but I suspect it may be 00/01/1900, which is zero formatted as a date.

01/01/1900 is 1 formatted as a date.

If the value shown is 01/01/1900 then your formula is returning 1 as the minimum. Without seeing the rest of your data I can't be sure whether the result is 0 or 1.Here is a possible solution.

This uses your array formula approach and just tests your original formula for a result of zero and if the result is zero it returns an empty cell instead:

{=IF(MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes", Today!$C2:$C$15000)))=0,"", MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes", Today!$C2:$C$15000))))}If you are getting a result of 1, then just use:

{=IF(MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes", Today!$C2:$C$15000)))<2,"", MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes", Today!$C2:$C$15000))))}Regards

You were right about the formatting. I was getting a zero. I added your addition to my formula and I am now getting a blank when the look up is not present. Thank you for your time and knowledge. I can always count on nice and experienced people like yourself.

Till next time,

Terry

You're very welcome. Regards

Humar

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History