find cell address that meets the criteria

[tkraju via OfficeKB.com]

Col a............... Col B

27-Aug-2005 ..................03-Mar-2006
10-Feb-2006
19-nov-2006
25-Jan-2007

I am looking for a function that gives cell address in range A1:A4 ,date that
is(nearest) greater than B1 date.

 

[Guest]

Try this entered as an array formula ctrl + shift + enter

=INDEX(A1:A6,MATCH(MIN(IF(A1:A6-B1>=0,A1:A6,FALSE)),IF(A1:A6-B1>=0,A1:A6,FALSE),0))

 

[Ron Rosenfeld]

Col a............... Col B

27-Aug-2005 ..................03-Mar-2006
10-Feb-2006
19-nov-2006
25-Jan-2007

I am looking for a function that gives cell address in range A1:A4 ,date that
is(nearest) greater than B1 date.

Your specification may be incomplete and is unclear to me.

=INDEX(A1:A4,MATCH(B1,A1:A4)+1)

will return the date that is next highest to the date in B1

=IF(VLOOKUP(B1,A1:A4,1)=B1,B1,INDEX(A1:A4,MATCH(B1,A1:A4)+1))

will return the smallest date that is equal to or greater than the date in B1.

The **array-entered** (<ctrl><shift><enter>) formula:

=INDEX(A1:A4,MATCH(MIN(ABS(B1-A1:A4)),ABS(B1-A1:A4),0))

will return the date that is nearest to the date in B1


Both formulas assume the dates in a1:a4 are sorted in ascending order.

Both formulas will give error messages if the date in B1 does not meet the
formula criteria.
--ron

 

[Niek Otten]

Sort the dates in Col A descending and use this formula:

=MATCH(B1,A1:A4,-1)

It gives 2 as an answer (second date;19-Nov-2006).

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| Col a............... Col B
|
| 27-Aug-2005 ..................03-Mar-2006
| 10-Feb-2006
| 19-nov-2006
| 25-Jan-2007
|
| I am looking for a function that gives cell address in range A1:A4 ,date that
| is(nearest) greater than B1 date.
|
| --
| Message posted via OfficeKB.com
| http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200702/1
|

 

[Guest]

Hi:

This gives the index number for the the entry:

=MATCH(B$1-1,$A$1:$A$4,1)+1

As this gives the cell address:

=ADDRESS(MATCH(B$1-1,$A$1:$A$4,1)+1,1)

It is a little bit of a mix becasue the match function wants the data sorted
in descending order for the match you wanted.

So I deducted one from the date and wound the one less than the date wanted
(ie the case where the date is the same as an entry and then I add 1 row on
to the row.

To work best sort in to decending aorder as try

25-Jan-07
19-Nov-06
10-Feb-06
27-Aug-05

=MATCH(B$1,$A$1:$A$4,-1)

There are problems at the limits. try it out.

 

[Ron Rosenfeld]

My first response omitted the addresses

Col a............... Col B

27-Aug-2005 ..................03-Mar-2006
10-Feb-2006
19-nov-2006
25-Jan-2007

I am looking for a function that gives cell address in range A1:A4 ,date that
is(nearest) greater than B1 date.

Your specification may be incomplete and is unclear to me.

=INDEX(A1:A4,MATCH(B1,A1:A4)+1)

will return the date that is next highest to the date in B1

=ADDRESS(MATCH(B1,A1:A4)+1,1)

will return the corresponding address


=IF(VLOOKUP(B1,A1:A4,1)=B1,B1,INDEX(A1:A4,MATCH(B1,A1:A4)+1))

will return the smallest date that is equal to or greater than the date in B1.

=ADDRESS(IF(COUNTIF(A1:A4,B1)>0,MATCH(B1,A1:A4),MATCH(B1,A1:A4)+1),1)

will return the corresponding address.



Finally, the **array-entered** (<ctrl><shift><enter>) formula:

=INDEX(A1:A4,MATCH(MIN(ABS(B1-A1:A4)),ABS(B1-A1:A4),0))

will return the date that is nearest to the date in B1

=ADDRESS(MATCH(MIN(ABS(B1-A1:A4)),ABS(B1-A1:A4)),1) (also **array-entered**)

will return the corresponding address.




Both formulas assume the dates in a1:a4 are sorted in ascending order.

Both formulas will give error messages if the date in B1 does not meet the
formula criteria.

--ron