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

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:A100)*(A3:A10),(A3:A10)))),A 3: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:A100)*(A3:A10),(A3:A 10)))),A3:B10,2,0)),"All
values are
negative.",VLOOKUP((MIN(IF((A3:A100)*(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:A100,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"
wrote in message
news:TheRobsterUK.1vztea_1127783101.6456@excelforu m-nospam.com...

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:A100,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"
wrote in message
news:TheRobsterUK.1vztea_1127783101.6456@excelforu m-nospam.com...

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:A100,
A3:A10)),A3:B10,2,0),"No positive number")}
FormulaArray

Bruno

use function <DMIN
see help
=======
"Bruno Campanini" wrote in message
...
"TheRobsterUK"
wrote in message
news:TheRobsterUK.1vztea_1127783101.6456@excelforu m-nospam.com...

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:A100,
A3:A10)),A3:B10,2,0),"No positive number")}
FormulaArray

Bruno

"R.VENKATARAMAN" wrote in message
...
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
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(Savings0,Savings)),Savings ,0))

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


--ron