Return minimum POSITVE value from range

If your first column of data is in A3:A10, and your second column in B3:B10,
I think this should do it.
=VLOOKUP((MIN(IF((A3:A10>0)*(A3:A10),(A3:A10)))),A3:B10,2,0)

This is an array formula and must be entered by pressing Ctrl+Shift+Enter.

This formula will return #N/A if all the values are negative. The formula
below should prevent the error. It's also an array formula.

=IF(ISNA(VLOOKUP((MIN(IF((A3:A10>0)*(A3:A10),(A3:A10)))),A3:B10,2,0)),"All
values are
negative.",VLOOKUP((MIN(IF((A3:A10>0)*(A3:A10),(A3:A10)))),A3:B10,2,0))
--
tj


"TheRobsterUK" wrote:

>
> Hi,
>
> Suppose I have a worksheet with the following data:
>
> (column1)......... (column2)
> Savings..................I.D.
> -243...................... 01
> -205...................... 02
> -165.......................03
> -87.........................04
> 57..........................05
> 109........................06
> 205........................07
> 303........................08
>
> What I need to do is look up the smallest -positive- number in the
> "Savings" column and the look over to the "I.D." column and retrieve
> the corresponding I.D.
>
> So in the above example, look up the value 57 and then retrieve the
> corresponding I.D. of 05.
>
> Is there a way to do this? I would have thought an array formula of
> some kind but I haven't met with any success so far. In my actual
> spreadsheet I have over 10,000 entries in the "Savings" column so any
> formula would need to be efficient.
>
> Thanks in advance
> -Rob
>
>
> --
> TheRobsterUK
>
>
> ------------------------------------------------------------------------
> TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
> View this thread: http://www.excelforum.com/showthread...hreadid=470975
>
>

Assuming data in cols A & B

Try something like this in say, C1:

=MIN(IF(A2:A10>0,B2:B10))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"TheRobsterUK" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> Suppose I have a worksheet with the following data:
>
> (column1)......... (column2)
> Savings..................I.D.
> -243...................... 01
> -205...................... 02
> -165.......................03
> -87.........................04
> 57..........................05
> 109........................06
> 205........................07
> 303........................08
>
> What I need to do is look up the smallest -positive- number in the
> "Savings" column and the look over to the "I.D." column and retrieve
> the corresponding I.D.
>
> So in the above example, look up the value 57 and then retrieve the
> corresponding I.D. of 05.
>
> Is there a way to do this? I would have thought an array formula of
> some kind but I haven't met with any success so far. In my actual
> spreadsheet I have over 10,000 entries in the "Savings" column so any
> formula would need to be efficient.
>
> Thanks in advance
> -Rob
>
>
> --
> TheRobsterUK
>
>
> ------------------------------------------------------------------------
> TheRobsterUK's Profile:
http://www.excelforum.com/member.php...fo&userid=9924
> View this thread: http://www.excelforum.com/showthread...hreadid=470975
>

Correction to earlier formula, sorry ..

Try something like this instead in say, C1:

=INDEX(B2:B10,
MATCH(MIN(IF(A2:A10>0,A2:A10)),A2:A10,0))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Adapt the ranges to suit ..

Note that in the event of any ties in the min value > 0,
the formula will return the 1st matched value from col B
(the one highest up), e.g., if you have say, 2 identical number 57's
in col A, then the value returned from col B will be 5
....
57 5
57 6
....

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"TheRobsterUK" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> Suppose I have a worksheet with the following data:
>
> (column1)......... (column2)
> Savings..................I.D.
> -243...................... 01
> -205...................... 02
> -165.......................03
> -87.........................04
> 57..........................05
> 109........................06
> 205........................07
> 303........................08
>

[...]

Another one:

{=IF(COUNTIF(A3:A10,">0"),VLOOKUP(MIN(IF(A3:A10>0,
A3:A10)),A3:B10,2,0),"No positive number")}
FormulaArray

Bruno

use function <DMIN>
see help
=======
"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...
> "TheRobsterUK" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Hi,
> >
> > Suppose I have a worksheet with the following data:
> >
> > (column1)......... (column2)
> > Savings..................I.D.
> > -243...................... 01
> > -205...................... 02
> > -165.......................03
> > -87.........................04
> > 57..........................05
> > 109........................06
> > 205........................07
> > 303........................08
> >
>
> [...]
>
> Another one:
>
> {=IF(COUNTIF(A3:A10,">0"),VLOOKUP(MIN(IF(A3:A10>0,
> A3:A10)),A3:B10,2,0),"No positive number")}
> FormulaArray
>
> Bruno
>
>

"R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
news:%[email protected]...
> use function <DMIN>
> see help

I don't like to set database, criteria fields etc. in Excel.
I'm prepared to use DMIN() (very, very rarely indeed, as all domain
functions!) in Access, not in Excel.

Ciao
Bruno

On Mon, 26 Sep 2005 19:22:08 -0500, TheRobsterUK
<[email protected]> wrote:

>
>Hi,
>
>Suppose I have a worksheet with the following data:
>
>(column1)......... (column2)
>Savings..................I.D.
>-243...................... 01
>-205...................... 02
>-165.......................03
>-87.........................04
>57..........................05
>109........................06
>205........................07
>303........................08
>
>What I need to do is look up the smallest -positive- number in the
>"Savings" column and the look over to the "I.D." column and retrieve
>the corresponding I.D.
>
>So in the above example, look up the value 57 and then retrieve the
>corresponding I.D. of 05.
>
>Is there a way to do this? I would have thought an array formula of
>some kind but I haven't met with any success so far. In my actual
>spreadsheet I have over 10,000 entries in the "Savings" column so any
>formula would need to be efficient.
>
>Thanks in advance
>-Rob



Array-enter:

=INDEX(ID,MATCH(MIN(IF(Savings>0,Savings)),Savings,0))

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula.


--ron