I need to find the highest value in column A < given value and > 0

[westend9876]

I need to find the highest value in a column a < given value
and > 0
AND
I need to find the lowest value in a column a > given value
and >0

I attached a sample workbook and highlighted the two cells in yellow where I want to retrieve the value

 

[PaulF]

=SMALL(A:A,COUNTIF(A:A,0)+1)
=LARGE(A:A,COUNTIF(A:A,0)-1)

See attachment... This work for you ??

Paul

 

[azumi]

Maybe:

=IF(B9="L",MAX(IF(((A1:A59<>0)*(A1:A59<D9)),A1:A59)),"")

=IF(B10="H",MIN(IF(A1:A59<>0,IF(A1:A59>D9,A1:A59))),"")

Array formula

 

[westend9876]

=SMALL(A:A,COUNTIF(A:A,0)+1)
=LARGE(A:A,COUNTIF(A:A,0)-1)

See attachment... This work for you ??

Paul

Hi Paul. There was a slight error in my explanation. I attached an updated workbook with the answer.

 

[westend9876]

Maybe:

=IF(B9="L",MAX(IF(((A1:A59<>0)*(A1:A59<D9)),A1:A59)),"")

=IF(B10="H",MIN(IF(A1:A59<>0,IF(A1:A59>D9,A1:A59))),"")

Array formula

Hi Azumi. This formula works. Is there anyway to accomplish my goal without an array?