Find Highest Date in a range

[LaDdIe]

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.

 

[Mike H]

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]

Thanks Mike, it works a treat
{=VLOOKUP(MAX(IF(DataSheetDate>=AI8,IF(DataSheetDate<=A8,DataSheetDate))),DataSheet!A8:AI2957,4,FALSE)}

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]

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:A27<=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]

Once again Thank you correctly interpreting my question and providing a
solution

Best wishes