Formula to retrieve values from duplicate data with multiple criteria

[ybr_15]

Hi, I have this data:

TEST 1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
2	DATA							RESULT
3
4	SKU	Title	Category	Contain Box	Rack	WH		SKU	Title	Category	Rack	WH
5	111201.067	Data 1	TK	210	RA13	420		111201.067	Data 1	TK	RA13	420
6	111201.069	Data 2	TK	210	RA13	210		111201.069	Data 2	TK	RA13	210
7	111201.081	Data 3	TK	40	B32	40		111201.081	Data 3	TK	B32	40
8	111201.082	Data 4	TK	60	B32	60		111201.082	Data 4	TK	B32	60
9	111201.083	Data 5	TK	30	A27	30		111201.083	Data 5	TK	A27	30
10	111201.084	Data 6	TK	27	A27	27		111201.084	Data 6	TK	A27	27
11	111201.085	Data 7	TK	110	A27	110		111201.085	Data 7	TK	A27	200
12	111201.085	Data 7	TK	200	A27	200		111201.086	Data 8	TK	A27	200
13	111201.085	Data 7	TK Total			310		111201.087	Data 9	TK	RA25	250
14	111201.086	Data 8	TK	200	A27	200		111201.089	Data 10	TK	RB05	260
15	111201.086	Data 8	TK	90	A27	90		111201.091	Data 11	TK	A07	250
16	111201.086	Data 8	TK Total			290
17	111201.087	Data 9	TK	250	RA25	250
18	111201.089	Data 10	TK	260	RB05	260
19	111201.091	Data 11	TK	250	A07	250
20	111201.091	Data 11	TK		RA14	250
Sheet1

Cell Formulas
Range	Formula
M5:M15	M5	=MAX(IF($B$5:$B$20=I5,IF($C$5:$C$20=J5,IF($D$5:$D$20=K5,$G$5:$G$20))))
Press CTRL+SHIFT+ENTER to enter array formulas.

I want to get the result as shown in the "RESULT" table based on the reference in the "DATA" table. I want to retrieve the data with the highest value using a formula with 3 criteria and get the result as shown in column "WH" (column M). The result of the formula is the same as I want. But, with the same criteria, I'm having a hard time getting a formula so that the results are as shown in the "Rack" column (column L) where the highest value comes from. Can anyone help me?. Thank you

 

[ybr_15]

Try this. I have altered the column N formula so that it matched the column P formula format. This might make it easier for you if you wanted to extend further beyond 3rd highest.
Colours were just for my benefit in checking

ybr_15.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3											1st Highest		2nd Highest		3rd Highest
4	SKU	Title	Category	Contain Box	Rack	WH		SKU	Title	Category	Rack	WH	Rack	WH	Rack	WH
5	111201.067	Data 1	TK	210	RA13	420		111201.067	Data 1	TK	RA13	420
6	111201.069	Data 2	TK	210	RA13	210		111201.069	Data 2	TK	RA13	210
7	111201.081	Data 3	TK	40	B32	40		111201.081	Data 3	TK	B32	40
8	111201.082	Data 4	TK	60	B32	60		111201.082	Data 4	TK	B32	60
9	111201.083	Data 5	TK	30	A27	30		111201.083	Data 5	TK	A27	30
10	111201.084	Data 6	TK	27	A27	27		111201.084	Data 6	TK	A27	27
11	111201.085	Data 7	TK	110	A27	110		111201.085	Data 7	TK	A27	200	A27	110
12	111201.085	Data 7	TK	200	A27	200		111201.086	Data 8	TK	A27	200	A27	90
13	111201.085	Data 7	TK Total			310		111201.087	Data 9	TK	RA25	250
14	111201.086	Data 8	TK	200	A27	200		111201.089	Data 10	TK	RB05	260
15	111201.086	Data 8	TK	90	A27	90		111201.091	Data 11	TK	A07	250	RA14	250
16	111201.086	Data 8	TK Total			290		121205.126	Data 12	PLATINUM	B24	120	B24	24	B24	20
17	111201.087	Data 9	TK	250	RA25	250		121205.159	Data 13	PLATINUM	B30	560	B28	120	B28	25
18	111201.089	Data 10	TK	260	RB05	260		121411.078	Data 14	GLOBAL	A11	66	A12	66	RB03	66
19	111201.091	Data 11	TK	250	A07	250
20	111201.091	Data 11	TK		RA14	250
21	111201.091	Data 11	TK Total			500
22	121205.126	Data 12	PLATINUM	20	B24	20
23	121205.126	Data 12	PLATINUM	24	B24	24
24	121205.126	Data 12	PLATINUM	40	B24	120
25	121205.126	Data 12	PLATINUM Total			164
26	121205.159	Data 13	PLATINUM	25	B28	25
27	121205.159	Data 13	PLATINUM	40	B28	120
28	121205.159	Data 13	PLATINUM	40	B30	560
29	121205.159	Data 13	PLATINUM Total			705
30	121411.078	Data 14	GLOBAL	66	A11	66
31	121411.078	Data 14	GLOBAL	66	A12	66
32	121411.078	Data 14	GLOBAL	66	RB03	66
33	121411.078	Data 14	GLOBAL Total			198
Sheet1

Cell Formulas
Range	Formula
L5:L18	L5	=INDEX(F$5:F$33,AGGREGATE(15,6,(ROW(F$5:F$33)-ROW(F$5)+1)/((B$5:B$33=I5)*(C$5:C$33=J5)*(D$5:D$33=K5)*(G$5:G$33=M5)),1))
M5:M18	M5	=AGGREGATE(14,6,G$5:G$33/((B$5:B$33=I5)*(C$5:C$33=J5)*(D$5:D$33=K5)),1)
N5:N18	N5	=IF(O5="","",INDEX(F$5:F$33,AGGREGATE(15,6,(ROW(F$5:F$33)-ROW(F$5)+1)/((B$5:B$33=I5)*(C$5:C$33=J5)*(D$5:D$33=K5)*(G$5:G$33=O5)),1+COUNTIFS($L$4:$M$4,"WH",L5:M5,O5))))
O5:O18	O5	=IFERROR(AGGREGATE(14,6,G$5:G$33/((B$5:B$33=I5)*(C$5:C$33=J5)*(D$5:D$33=K5)),2),"")
P5:P18	P5	=IF(Q5="","",INDEX(F$5:F$33,AGGREGATE(15,6,(ROW(F$5:F$33)-ROW(F$5)+1)/((B$5:B$33=I5)*(C$5:C$33=J5)*(D$5:D$33=K5)*(G$5:G$33=Q5)),1+COUNTIFS($L$4:$O$4,"WH",L5:O5,Q5))))
Q5:Q18	Q5	=IFERROR(AGGREGATE(14,6,G$5:G$33/((B$5:B$33=I5)*(C$5:C$33=J5)*(D$5:D$33=K5)),3),"")

Hi, Peter_SSs

Thanks for the improvement on the formula, the solution you provided really helped my problem. Now I can make formulas for the 4th etc. Thanks again and have a nice weekend

 

[Peter_SSs]

Cheers. Glad it helped.