| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Highest Date in a range
Hello
Criteria dates; (Sheet 1) A1 = 01/01/2009, A2 = 31/01/2009 (Sheet 2) A1 to A27 list of dates 01/01/2009 to 27/01/2009 I need a formula that will find cell with the highest date in the range between the criteria dates, and the return the value of the adjacent cell (Sheet 2) Col B. Thank you. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Highest Date in a range
Hi,
Try this ARRAY formula which would go on Sheet1 =VLOOKUP(MAX(IF(Sheet2!A1:A27=A1,IF(Sheet2!A1:A27 <=A2,Sheet2!A1:A27))),Sheet2!A1:B27,2,FALSE) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LaDdIe" wrote: Hello Criteria dates; (Sheet 1) A1 = 01/01/2009, A2 = 31/01/2009 (Sheet 2) A1 to A27 list of dates 01/01/2009 to 27/01/2009 I need a formula that will find cell with the highest date in the range between the criteria dates, and the return the value of the adjacent cell (Sheet 2) Col B. Thank you. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Highest Date in a range
Thanks Mike, it works a treat!
{=VLOOKUP(MAX(IF(DataSheetDate=AI8,IF(DataSheetDa te<=A8,DataSheetDate))),DataSheet!A8:AI2957,4,FALS E)} If the value being looked up is nothing can the results be returned as nothing, in this example the looked up value is a date and the destination cell is formated as a date and so where the looked up value is nothing it returns 00/01/1900. Thank you "Mike H" wrote: Hi, Try this ARRAY formula which would go on Sheet1 =VLOOKUP(MAX(IF(Sheet2!A1:A27=A1,IF(Sheet2!A1:A27 <=A2,Sheet2!A1:A27))),Sheet2!A1:B27,2,FALSE) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LaDdIe" wrote: Hello Criteria dates; (Sheet 1) A1 = 01/01/2009, A2 = 31/01/2009 (Sheet 2) A1 to A27 list of dates 01/01/2009 to 27/01/2009 I need a formula that will find cell with the highest date in the range between the criteria dates, and the return the value of the adjacent cell (Sheet 2) Col B. Thank you. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Highest Date in a range
Hi,
Do you mean if the 'returned' value is zero? If so the formula gets a bit long but you simply wrap it in an IF statement =IF(VLOOKUP(MAX(IF(Sheet2!A1:A27=A1,IF(Sheet2!A1: A27<=A2,Sheet2!A1:A27))),Sheet2!A1:B27,2,FALSE)=0, "",VLOOKUP(MAX(IF(Sheet2!A1:A27=A1,IF(Sheet2!A1:A 27<=A2,Sheet2!A1:A27))),Sheet2!A1:B27,2,FALSE)) Still an ARRAY formula -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LaDdIe" wrote: Thanks Mike, it works a treat! {=VLOOKUP(MAX(IF(DataSheetDate=AI8,IF(DataSheetDa te<=A8,DataSheetDate))),DataSheet!A8:AI2957,4,FALS E)} If the value being looked up is nothing can the results be returned as nothing, in this example the looked up value is a date and the destination cell is formated as a date and so where the looked up value is nothing it returns 00/01/1900. Thank you "Mike H" wrote: Hi, Try this ARRAY formula which would go on Sheet1 =VLOOKUP(MAX(IF(Sheet2!A1:A27=A1,IF(Sheet2!A1:A27 <=A2,Sheet2!A1:A27))),Sheet2!A1:B27,2,FALSE) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LaDdIe" wrote: Hello Criteria dates; (Sheet 1) A1 = 01/01/2009, A2 = 31/01/2009 (Sheet 2) A1 to A27 list of dates 01/01/2009 to 27/01/2009 I need a formula that will find cell with the highest date in the range between the criteria dates, and the return the value of the adjacent cell (Sheet 2) Col B. Thank you. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Highest Date in a range
Once again Thank you correctly interpreting my question and providing a
solution Best wishes "Mike H" wrote: Hi, Do you mean if the 'returned' value is zero? If so the formula gets a bit long but you simply wrap it in an IF statement =IF(VLOOKUP(MAX(IF(Sheet2!A1:A27=A1,IF(Sheet2!A1: A27<=A2,Sheet2!A1:A27))),Sheet2!A1:B27,2,FALSE)=0, "",VLOOKUP(MAX(IF(Sheet2!A1:A27=A1,IF(Sheet2!A1:A 27<=A2,Sheet2!A1:A27))),Sheet2!A1:B27,2,FALSE)) Still an ARRAY formula -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LaDdIe" wrote: Thanks Mike, it works a treat! {=VLOOKUP(MAX(IF(DataSheetDate=AI8,IF(DataSheetDa te<=A8,DataSheetDate))),DataSheet!A8:AI2957,4,FALS E)} If the value being looked up is nothing can the results be returned as nothing, in this example the looked up value is a date and the destination cell is formated as a date and so where the looked up value is nothing it returns 00/01/1900. Thank you "Mike H" wrote: Hi, Try this ARRAY formula which would go on Sheet1 =VLOOKUP(MAX(IF(Sheet2!A1:A27=A1,IF(Sheet2!A1:A27 <=A2,Sheet2!A1:A27))),Sheet2!A1:B27,2,FALSE) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LaDdIe" wrote: Hello Criteria dates; (Sheet 1) A1 = 01/01/2009, A2 = 31/01/2009 (Sheet 2) A1 to A27 list of dates 01/01/2009 to 27/01/2009 I need a formula that will find cell with the highest date in the range between the criteria dates, and the return the value of the adjacent cell (Sheet 2) Col B. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare number to range; find highest positive difference | Excel Worksheet Functions | |||
| Find closest match and return next highest number in range | Excel Discussion (Misc queries) | |||
| Rolling up the latest date, criteria of range to compare highest d | Excel Programming | |||
| Julian date - find next highest date/number | Excel Worksheet Functions | |||
| Find date and copy range based on that date | Excel Programming | |||
Linear Mode
