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

user_15081054 0
Viewed 41

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

Capture.jpg

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.

excel-formula excel
1 Answers

Use FILTER() with MAX() formula.

 =FILTER(A2:A6,C2:C6=MAX(C2:C6))

enter image description here

Edit: To display result in single cell with comma separated use below formula.

 =TEXTJOIN(", ",TRUE,FILTER(A2:A6,C2:C6=MAX(C2:C6)))
user_55147470
Related