find cell address that meets the criteria
My first response omitted the addresses
On Mon, 05 Feb 2007 12:35:13 GMT, "tkraju via OfficeKB.com" <u16627@uwe wrote:
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),MA TCH(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
|