Max based on conditions

[lejanco]

Hello,

I am looking for a formula for A50 in summary sheet new that will find the Max value in the Inventory Turnover sheet of Column H, based on the condition that its corresponding cell in column C s greater than 0, and returns its corresponding item number to cell A50 on the summary sheet page. Here is my formula, but it is not ignoring the 0s in column C. Thanks!
=INDEX('Inventory Turnover'!A:A,MATCH(MAX(IF('Inventory Turnover'!C:C<>0,'Inventory Turnover'!H:H,""),'Inventory Turnover'!H:H),'Inventory Turnover'!H:H,0),1)

[daddylonglegs]

Try this array formula

=INDEX('Inventory Turnover'!A:A,MATCH(1,('Inventory Turnover'!H:H=MAX(IF('Inventory Turnover'!C:C<>0,'Inventory Turnover'!H:H)))*('Inventory Turnover'!C:C<>0),0))

....although it's probably better to use a fixed range, e.g. 1000 rows, otherwise the formula will be slow referencing whole columns.....

[lejanco]

Worked! Why the Match(1,) if you don't mind me asking.

[daddylonglegs]

In the formula there are two conditions multiplied in the MATCH function (shown in blue and red)

=INDEX('Inventory Turnover'!A:A,MATCH(1,('Inventory Turnover'!H:H=MAX(IF('Inventory Turnover'!C:C<>0,'Inventory Turnover'!H:H)))*('Inventory Turnover'!C:C<>0),0))

So when you multiply TRUE/FALSE arrays you only get 1 when both conditions are TRUE......so the MATCH is with 1 because that finds the first row where both conditions are TRUE

The conditions are, effectively

Column H is equal to the max value of col H when Col C is > 0

and

Col C > 0

[lejanco]

Thank you!