How to list out the location of duplicated maximum value in Excel?

[alantse2010]

I have the table to show the NC quantity and C quantity of item 1.1a - 1.4e in excel file.


I want to find out the maximum value of C, that i use below formula in H4:

=MAX(C4:C22)

that show the maximum value of C is 8, then i want to find the maximum value corresponding to which item, so i use below formula in H5:

=(INDEX($A$4:$A$22,MATCH(H4,$C$4:$C$22,0)))

It shows only one result which is 1.1a, how can i list out all the item which has the maximum value which i use for vlookup the information?

Please find the Capture.jpg for your reference. Thank you very much for your help.

 

[aRandomHelper]

Are you okay with a VBA solution?
If yes, how do you want to display them? down column H? or across row 5?
Since you're looking up the description, maybe show how the output should look like with more than 1 match/result.

 

[alantse2010]

Are you okay with a VBA solution?
If yes, how do you want to display them? down column H? or across row 5?
Since you're looking up the description, maybe show how the output should look like with more than 1 match/result.

Both of formula and VBA are OK. i want to display the all the corresponding item on next column I, J, K...etc

Could i use the FILTER formula to list out?

Thank you.

 

[alz]

Hi try This!

TempO.xlsx
	A	B	C	D	E	F	G	H
1
2		Overal Data
3	ITEM	NC quantity	C Quantity
4	1.1a	0	8				Maixum Calue of C	8
5	1.1b	5	3				Maixum Value of C corresponded item Count	6
6	1.1c	0	8				Maixum Value of C corresponded item	1.1a
7	1.1d	0	8					1.1c
8	1.1e	0	8					1.1d
9	1.1f	0	8					1.1e
10	1.2a	0	0					1.1f
11	1.2b	7	0					1.4a
12	1.2c	8	0
13	1.2d	0	5
14	1.2e	0	0
15	1.2f	0	4
16	1.3a	0	0
17	1.3b	8	0
18	1.3c	8	0
19	1.3d	0	2
20	1.3e	0	0
21	1.3f	0	0
22	1.4a	0	8
23	1.4b	0	0
24	1.4c	0	1
25	1.4d	0	2
26	1.4e	0	3
27	1.4f	0	4
Sheet4

Cell Formulas
Range	Formula
H4	H4	=MAX($C$4:$C$27)
H5	H5	=COUNTIF($C$4:$C$27,H4)
H6:H14	H6	=IF(ROWS($H$6:H6)>$H$5,"",INDEX($A$4:$A$27,AGGREGATE(15,6,IF($C$4:$C$27=$H$4,ROW($A$4:$A$27)-ROW($A$4)+1),ROWS($H$6:H6))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Phuoc]

Or try this:

=TEXTJOIN(" -",TRUE,IF($C$4:$C$22=MAX($C$4:$C$22),$A$4:$A$22,""))

Enter with Ctrl+Shift+Enter.

 

[Peter_SSs]

Could i use the FILTER formula to list out?

That sounds like you have Excel 365 & if so the answer is 'yes'
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

i want to display the all the corresponding item on next column I, J, K...etc

21 01 26.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3	ITEM	NC quantity	C quantity
4	1.1a	0	8				Maixum Calue of C	8
5	1.1b	5	3				Maixum Value of C correspond item	1.1a	1.1c	1.1d	1.1e	1.1f
6	1.1c	0	8
7	1.1d	0	8
8	1.1e	0	8
9	1.1f	0	8
10	1.2a	0	0
11	1.2b	7	0
12	1.2c	8	0
13	1.2d	0	5
14	1.2e	0	0
15	1.3a	0	4
16	1.3b	8	0
17	1.3c	8	0
18	1.4a	0	0
19	1.4b	0	2
20	1.4c	0	0
21	1.4d	0	0
22	1.4e	0	0
Max Values

Cell Formulas
Range	Formula
H4	H4	=MAX($C$4:$C$22)
H5:L5	H5	=TRANSPOSE(FILTER(A4:A22,C4:C22=H4,""))
Dynamic array formulas.

 

[Dossfm0q]

Book1
	A	B	C	D	E	F	G	H	I
1
2
3	ITEM	NC	C
4	1.1a	0	8					8	1.1a
5	1.1b	5	3						1.1c
6	1.1c	0	8						1.1d
7	1.1d	0	8						1.1e
8	1.1e	0	8						1.1f
9	1.1f	0	8
10	1.2a	0	0
11	1.2b	7	0
12	1.2c	8	0
13	1.2d	0	5
14	1.2e	0	0
15	1.3a	0	4
16	1.3b	8	0
17	1.3c	8	0
18	1.4a	0	0
19	1.4b	0	2
20	1.4c	0	0
21	1.4d	0	0
22	1.4e	0	0
Sheet1

Cell Formulas
Range	Formula
H4	H4	=MAX(C:C)
I4:I22	I4	=IFERROR(INDEX($A$4:$A$22,AGGREGATE(15,6,(ROW($A$4:$A$22)-ROW($A$4)+1)/($C$4:$C$22=$H$4),ROWS($G$4:G4))),"")

 

[Peter_SSs]

@alantse2010
Glad to see that it worked for you, and thanks for updating your account details.

 

[alantse2010]

Thank you all very much for your help. Everyone answer is work.

It is sorry that it cannot choose more than one reply as best post.