How to select the value based on given condition?

[Guest]

Does anyone have any suggestions on how to select the highest value for
specific date within specific month?
In A column, there is a list of date, and in B column, there is a list of
value, and
in cell C1, there is a given date.

I would like to select the date with the highest value within the given
month in cell C1, for example

6 Oct 07 65 30 Oct 07
5 Oct 07 35
4 Oct 07 44
3 Oct 07 88
2 Oct 07 65
1 Oct 07 17
30 Sep 07 53
29 Sep 07 25
28 Sep 07 44
27 Sep 07 96
26 Sep 07 76

Given date is 30 Oct 07, I would select 3 Oct 07, because it is the highest
value in column within the given month Oct, and the date 3 Oct 07 returns in
cell D1.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Guest]

Try this array* formula in D1:

=MAX(IF((MONTH(A$1:A$100)=MONTH($C$1))*(YEAR(A$1:A$100)=YEAR($C$1)),B$1:B$100,0))

to return the maximum value, and then use an INDEX/MATCH formula in E1 to
return the matching date.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT_ENTER to commit it, rather than
the usual ENTER. If you do this correctly, then Excel will wrap curly braces
{ } around the formula when viewed in the formula bar - you must not type
these yourself.

Hope this helps.

Pete

 

[Roger Govier]

Hi Eric

Enter the following array formula into cell D1
{=INDEX(A1:A11,MATCH(MAX(IF(TEXT(A1:A11,"yymm")
=TEXT(C1,"yymm"),B1:B11,"")),B1:B11,0))}

To enter or amend array formulae, use Control+Shift+Enter (CSE) not just
Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula. Do not enter them yourself.