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