Return the max value considering criteria in multiple columns

[Harry_]

I would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.

I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).

For example in cell M7:
=IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0)))
CTRL + SHIFT + ENTER


- -

Can anyone reccommend an alternate formula that will not falter where mine does?

Below is a snapshot of my Data, if that helps.



[TABLE="width: 1846"]
<tbody>[TR]
[TD]Style Code[/TD]
[TD]State[/TD]
[TD]Location Code[/TD]
[TD]Location Name[/TD]
[TD]Desc[/TD]
[TD]Max[/TD]
[TD]Current OH[/TD]
[TD]Last X Weeks Sales[/TD]
[TD]Sum of W OH[/TD]
[TD]State[/TD]
[TD]Required[/TD]
[TD]Available[/TD]
[TD]Send To[/TD]
[TD]Units Still Required[/TD]
[TD]New OH Units[/TD]
[TD]Difference[/TD]
[TD]Style Code[/TD]
[/TR]
[TR]
[TD]1000044573[/TD]
[TD]NSW[/TD]
[TD]0009[/TD]
[TD]GP Macquarie[/TD]
[TD]FARAH SHORT BOOT BLACK[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]GP Bondi Junction[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]1000044573[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0021[/TD]
[TD]GP Chatswood[/TD]
[TD]FARAH SHORT BOOT BLACK[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD]1000044573[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0032[/TD]
[TD]GP Castle Hill[/TD]
[TD]FARAH SHORT BOOT BLACK[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]1000044573[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0034[/TD]
[TD]GP Macarthur Square[/TD]
[TD]FARAH SHORT BOOT BLACK[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]NSW[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]1000044573[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0039[/TD]
[TD]GP Burwood[/TD]
[TD]FARAH SHORT BOOT BLACK[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]1000044573[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0040[/TD]
[TD]GP Hornsby[/TD]
[TD]FARAH SHORT BOOT BLACK[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]1000044573[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0043[/TD]
[TD]GP Erina[/TD]
[TD]FARAH SHORT BOOT BLACK[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]NSW[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]1000044573[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0045[/TD]
[TD]GP Bondi Junction[/TD]
[TD]FARAH SHORT BOOT BLACK[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]NSW[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]1000044573[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

It's a hard task to match the formula you posted to your exhibit.

Z7, ontrol+shift+enter and copy down:

=IFERROR(INDEX(D$7:D$999,SMALL(IF(J$7:J$999=State,IF(Q$7:Q$999=StyleCode,
  IF(N$7:N$999=MAX(IF(J$7:J$999=State,IF(Q$7:Q$999=StyleCode,N$7:N$999))),
  ROW(D$7:D$999)-ROW(D$7)+1))),ROWS(Z$7:Z7))),"")