# Find Max Value, If Ciriteria Is Met

#### peterhinton

##### Active Member
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>

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### gaz_chops

##### Well-known Member
Is this what you're after?

Code:
``````[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.

Last edited:

#### peterhinton

##### Active Member
Perfect Dude, Thank You !

#### AliGW

##### Banned
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).

You're welcome.

Replies
3
Views
129
Replies
8
Views
654
Replies
5
Views
739
Replies
4
Views
569
Replies
2
Views
436

1,191,117
Messages
5,984,739
Members
439,906
Latest member
Sekiro1899

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back