Excel Lookup and choose to return the largest value of a each month

[AlGuy]

I used the formula below to get the last monthly price of an item
={LOOKUP(9.9999999E+307,CHOOSE({1,2},0,LOOKUP(2,1/((Market!$I$2:$I$9998<>"")*(TEXT(Market!$A$2:$A$9998,"mmm-yyyy")=B11&"-"&A11)),Market!$I$2:$I$9998)))}
In fact, I have 2 separate columns for year and month (referenced by B11 and A11) and the values are on a separate sheet.

The formula works great but now, I would like to do the same thing but return the largest value of each month without having to change the formula.
For example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Thursday, May 11, 2006[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Friday, May 12, 2006[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Monday, May 15, 2006[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tuesday, May 16, 2006[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Wednesday, May 17, 2006[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Thursday, May 18, 2006[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Friday, May 19, 2006[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 153"]
<colgroup><col></colgroup><tbody></tbody>[/TABLE]

Based on the table below, the formula should return the value of 10. I can't simply use max because the days can change.

Is it possible? If not, what is the best dynamic formula, assuming that the days can change in the separate sheet.

Thank you!!!!!!

 

[Aladin Akyurek]

1.

Define BigNum as referring to:

=9.99999999999999E+307

Change the formula you have to:

=LOOKUP(BigNum,CHOOSE({1,2},0,LOOKUP(BigNum,1/((Market!$I$2:$I$9998<>"")*
  (TEXT(Market!$A$2:$A$9998,"mmm-yyyy")=B11&"-"&A11)),Market!$I$2:$I$9998)))

2. What is the relation of this formula to the exhibit you posted? Why not use the data this exhibit shows the max for each month year?

 

[AlGuy]

Aladin,

Thank you (once again)for your suggestion. Unfortunately defining the BigNum as the last value of a series does not solve my problem. I am going to use the max formula instead, even if it will be time consuming and not dynamic.

Happy new year!

 

[Aladin Akyurek]

Aladin,

Thank you (once again)for your suggestion. Unfortunately defining the BigNum as the last value of a series does not solve my problem. I am going to use the max formula instead, even if it will be time consuming and not dynamic.

Happy new year!

But what is the problem? And the max formula that solves it?

 

[AlGuy]

But what is the problem? And the max formula that solves it?

Aladin,

Sorry for the delay. I have just tried to find the single annual value in a column of more than 1,000 rows which is always moving. I wanted to find a smart way to return the values knowing that each value belongs to a single year.

Just to be more precise, assume you have in a single column dividend amount for a company over 15 years and each row of the cell spreadsheet is a day (date in column A and dividend value in column B). Since the dividend is paid only yearly (in my case), I was hoping to get the single value within a year range, identified by the values in column A.

 

[Aladin Akyurek]

Aladin,

Sorry for the delay. I have just tried to find the single annual value in a column of more than 1,000 rows which is always moving. I wanted to find a smart way to return the values knowing that each value belongs to a single year.

Just to be more precise, assume you have in a single column dividend amount for a company over 15 years and each row of the cell spreadsheet is a day (date in column A and dividend value in column B). Since the dividend is paid only yearly (in my case), I was hoping to get the single value within a year range, identified by the values in column A.

Not sure I follow... Say that we want to look at the month of january in 2013. Are you after the max value in column B while date values are in column A?

 

[AlGuy]

Not sure I follow... Say that we want to look at the month of january in 2013. Are you after the max value in column B while date values are in column A?

01/01/2010

 

[AlGuy]

Not sure I follow... Say that we want to look at the month of january in 2013. Are you after the max value in column B while date values are in column A?

Exactly. In fact, I am after the only value in B that matches the year in another cell. The formula below was successful at doing that for each end of the month value {LOOKUP(9.9999999E+307,CHOOSE({1,2},0,LOOKUP(2,1/((Market!$I$2:$I$9998<>"")*(TEXT(Market!$A$2:$A$9998,"mmm-yyyy")=B11&"-"&A11)),Market!$I$2:$I$9998))) where the values are in the Market sheet and the criteria (month and year) are A11 and B11.

 

[Aladin Akyurek]

Not sure I follow... Say that we want to look at the month of january in 2013. Are you after the max value in column B while date values are in column A?

01/01/2010

Exactly. In fact, I am after the only value in B that matches the year in another cell. The formula below was successful at doing that for each end of the month value {LOOKUP(9.9999999E+307,CHOOSE({1,2},0,LOOKUP(2,1/((Market!$I$2:$I$9998<>"")*(TEXT(Market!$A$2:$A$9998,"mmm-yyyy")=B11&"-"&A11)),Market!$I$2:$I$9998))) where the values are in the Market sheet and the criteria (month and year) are A11 and B11.

Control+shift+enter, not just enter:

=MAX(IF(TEXT(Market!$A$2:$A$9998,"mmm-yyyy")=B11&"-"&A11,
  Market!$I$2:$I$9998))

Does this meet your requirement?