This is formula for ascending order but this give 24/04/07 for 17.

=IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)))," ",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)))


This will give correct result BUT not if value is = to last in table i.e 40
or mo

=IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) ,"",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1))

No more ideas!

HTH


"Jaydubs" wrote:

Dear Toppers,

And in Ascending order?

Thanks !
--
** Fool on the hill **


"Toppers" wrote:

17 gave me 25/04/07 .. data must be DESCENDING ORDER.

39 gave 28/04/07


28/04/2007 40 19
27/04/2007 35 15
26/04/2007 28 12
25/04/2007 23 11
24/04/2007 16 9
23/04/2007 10 8

"Jaydubs" wrote:

Hello Topper,

Thanks for your solution, unfortunately it gives me a day before the actual
day of reaching the number required. In the example below it gave me 24-04-07
instead of the required 25-04-07 (column B).


--
** Fool on the hill **


"Toppers" wrote:

PROVIDING the data in columns A, B & C are is DESCENDING sequence this will
work

=IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,-1))),"",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,-1)))

A value greater than 40 (to use yoour example) will return an error value of
blank.

HTH


"Jaydubs" wrote:

Hello thanx for your previous reply, I figured it out, but what happens if
the number does not appear in either Column B or C.....?

No I would appreciate a similar formula, which looks for the nearest number
compared to the 17 (for instance 18 in the example below). In this example
the first time 17 or higher is hit is 25-07-07 in Column B and 28-04-07 in
Column C.

A B C
---------------------------------------------
23-04-07 10 8
24-04-07 16 9
25-04-07 23 11
26-04-07 28 12
27-04-07 35 15
28-04-07 40 19

Can you please help again?
--
** Fool on the hill **


"Jaydubs" wrote:

Unfortunately I am working with a dutch version, so I need to translate some
key elements in the formula.

What is ?
INDEX
ISNA
MATCH

--
** Fool on the hill **
"Toppers" wrote:

If D2=17 then:

=IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0)))

=IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0)))

HTH

"Jaydubs" wrote:

Dear Excel(lent) users,

I have a sheet in which I have the following columns:
A : Date
B: Number range, cummulatif +3 (positive estimation)
C: Number range, cummulatif -2 (negative estimation)

Now I want to know the following:
I want to know the date in A, which is the first date a certain number is
shown in Column B.

For instance:
A B C
---------------------------------------------
23-04-07 10 8
24-04-07 17 9
25-04-07 23 11
26-04-07 28 12
27-04-07 35 15
28-04-07 40 17
etc etc

The first time the number 17 appears in column B is on 24-04-07 so I want
that date as a return, the first time the number 17 appears in column C is on
28-04-07 so I want that date as a return.

Can you please help me ??



--
** Fool on the hill **