Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 5th 07, 12:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 109
Default find cell address that meets the criteria

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.a...excel/200702/1


  #2   Report Post  
Old February 5th 07, 12:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,101
Default find cell address that meets the criteria

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))

"tkraju via OfficeKB.com" 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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200702/1


  #3   Report Post  
Old February 5th 07, 12:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default find cell address that meets the criteria

On Mon, 05 Feb 2007 12:35:13 GMT, "tkraju via OfficeKB.com" <[email protected] 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

=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
  #4   Report Post  
Old February 5th 07, 01:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default find cell address that meets the criteria

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



"tkraju via OfficeKB.com" <[email protected] wrote in message news:[email protected]
| 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.a...excel/200702/1
|


  #5   Report Post  
Old February 5th 07, 01:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 694
Default find cell address that meets the criteria

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"tkraju via OfficeKB.com" 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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200702/1




  #6   Report Post  
Old February 5th 07, 01:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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" <[email protected] 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I get a cell to Flash if it meets a certain criteria in Ex tcfoote Excel Worksheet Functions 1 October 31st 06 04:03 PM
sum every other cell in a row if next cell meets certain criteria g4rod Excel Worksheet Functions 3 October 3rd 06 05:10 PM
How do I go to the next cell that meets criteria Neil Excel Discussion (Misc queries) 3 May 12th 06 04:34 PM
how can I highlight a cell if it meets year and month criteria Clyde Excel Worksheet Functions 2 May 11th 06 02:24 PM
Find max number of character and return cell address ExcelMonkey Excel Worksheet Functions 5 April 15th 06 04:13 AM


All times are GMT +1. The time now is 09:36 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017