Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
find cell address that meets the criteria
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 =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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" <u16627@uwe wrote in message news:6d59c488fea70@uwe... | 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I get a cell to Flash if it meets a certain criteria in Ex | Excel Worksheet Functions | |||
sum every other cell in a row if next cell meets certain criteria | Excel Worksheet Functions | |||
How do I go to the next cell that meets criteria | Excel Discussion (Misc queries) | |||
how can I highlight a cell if it meets year and month criteria | Excel Worksheet Functions | |||
Find max number of character and return cell address | Excel Worksheet Functions |