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.

Style Code	State	Location Code	Location Name	Desc	Max	Current OH	Last X Weeks Sales	Sum of W OH	State	Required	Available	Send To	Units Still Required	New OH Units	Difference	Style Code
1000044573	NSW	0009	GP Macquarie	FARAH SHORT BOOT BLACK	4	1	0		NSW		1	GP Bondi Junction		1	0	1000044573
		0021	GP Chatswood	FARAH SHORT BOOT BLACK	4	5	0		NSW		5			5	0	1000044573
		0032	GP Castle Hill	FARAH SHORT BOOT BLACK	4	0	0		NSW		0			0	0	1000044573
		0034	GP Macarthur Square	FARAH SHORT BOOT BLACK	5	0	1	0	NSW	5	0		5	0	0	1000044573
		0039	GP Burwood	FARAH SHORT BOOT BLACK	5	4	0		NSW		4			4	0	1000044573
		0040	GP Hornsby	FARAH SHORT BOOT BLACK	4	3	0		NSW		3			3	0	1000044573
		0043	GP Erina	FARAH SHORT BOOT BLACK	6	0	1	0	NSW	6	0		6	0	0	1000044573
		0045	GP Bondi Junction	FARAH SHORT BOOT BLACK	8	0	1	0	NSW	8	0		7	1	1	1000044573

<tbody>
</tbody>

 

[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))),"")