A1 = [NIL] B1 = [NIL]
A2 = 18-Jan-07 B2 = 1
A3 = [NIL] B3 = [NIL]
A4 = 20-Jan-07 B4 = 1
A5 = 24-Jan-07 B5 = -1
A6 = [NIL] B6 = [NIL]
A7 = 26-Jan-07 B7 = 1
A8 = [NIL] B8 = [NIL]
A9 = 28-Jan-07 B9 = -1

I would like to look for the smallest value in A:A colume, in this case
18-Jan-07 in cell A2, then return the value of 1 in cell B2 based on the same
row and put it into cell C1.

I would like to look for the second smallest value in A:A colume, in this
case 20-Jan-07 in cell A4, then return the value of 1 in cell B4 based on the
same row and put it into cell C2.

Does anyone have any suggestion?
Thank you in advance
Eric

One way:

C1: =VLOOKUP(SMALL(A:A,ROW()),A:B,2,FALSE)

Copy down to C2 or as far as required.

Note that if you have duplicate dates, only the value corresponding to
the first instance will be returned.


In article ,
Eric wrote:

A1 = [NIL] B1 = [NIL]
A2 = 18-Jan-07 B2 = 1
A3 = [NIL] B3 = [NIL]
A4 = 20-Jan-07 B4 = 1
A5 = 24-Jan-07 B5 = -1
A6 = [NIL] B6 = [NIL]
A7 = 26-Jan-07 B7 = 1
A8 = [NIL] B8 = [NIL]
A9 = 28-Jan-07 B9 = -1

I would like to look for the smallest value in A:A colume, in this case
18-Jan-07 in cell A2, then return the value of 1 in cell B2 based on the same
row and put it into cell C1.

I would like to look for the second smallest value in A:A colume, in this
case 20-Jan-07 in cell A4, then return the value of 1 in cell B4 based on the
same row and put it into cell C2.

Does anyone have any suggestion?
Thank you in advance
Eric