Find Max Value, If Ciriteria Is Met

[peterhinton]

Morning All,

Can anyone help, probably quite an easy one for the veterans with arrays,

I have the below table, I want to return the highest week for any given month,

lets just use month 2 for an example, return value would be 201609,

my data starts in A1, month number will be in cell C1, and result can go anywhere,

Week	Month
201601	1
201602	1
201603	1
201604	1
201605	2
201606	2
201607	2
201608	2
201609	2
201610	3
201611	3
201612	3
201613	3

<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

 

[gaz_chops]

Is this what you're after?

[TABLE="width: 390"]
<tbody>[TR]
[TD="width: 65"][/TD]
[TD="width: 65"]Week[/TD]
[TD="width: 65"]Month[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"]Month[/TD]
[TD="width: 65"]Week[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201601[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]201609[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201602[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201603[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201604[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201605[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201606[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201607[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201608[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201609[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201610[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201611[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201612[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]201613[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=MAX(IF(C2:C14=E2,B2:B14))
Enter as an array

Note: E2 = the month you want result for.

 

[peterhinton]

Perfect Dude, Thank You !

 

[AliGW]

Try this:

=INDEX($A$2:$A$14,MATCH(E2,$B$2:$B$14,0)+COUNTIF($B$2:$B$14,E2)-1,0)

E2 contains the week number you want to query (e.g. 2).

 

[gaz_chops]

You're welcome.