Find names corresponding to top 5 age (with multiple same age)

[roman22]

Hi all

I was trying to get the site names corresponding to top 5 age (days). However, my formula below is not returning me blank cell.(Attached file)

=IFERROR(INDEX('Capacity Data'!$C$2:$C$3113,MATCH(1,INDEX(('Output'!B3:B7=LARGE('Capacity Data'!$H$2:$H$3113,ROWS('Capacity Data'!$C$1:C3113)))*(COUNTIF('Output'!A3:A7,'Capacity Data'!$C$2:$C$3113)=0)*(COUNTIF('Capacity Data'!$G$2:$G$3113,$D$3)=0),),0)),"")

When I am trying to get the 'Site Name' for Two same age it throws error.

Thanks for helping!

[azumi]

Perhaps, for "Site" name, put this on A3 and copied down:

=IF(ROWS($A$1:A1)<=5,IFERROR(INDEX('Capacity Data'!$C$3034:$C$3110,MATCH(1,INDEX(('Capacity Data'!$H$3034:$H$3110=LARGE('Capacity Data'!$H$3034:$H$3110,ROWS($A$2:A2)))*(COUNTIF($A$2:A2,'Capacity Data'!$C$3034:$C$3110)=0)*('Capacity Data'!$G$3034:$G$3110=$D$3),),0)),""),"")

For "Age", put this on B3 and copied down:

=IFERROR(VLOOKUP(A3,'Capacity Data'!$C$3034:$H$3110,6,FALSE),"")

[roman22]

Thanks Azumi for your reply.

Just trying to understand the changes that you suggested, on the 'ROWS($A$1:A5)<=5' section. Why are we starting from position 1 to position 5?

Also, why are we looking at 'ROWS($A$2:A6))' further down in the function when we are desired to populate the data from A3 to B7 (attached output in previous post)



Thanks for helping

[azumi]

Because in the title you want the 5 biggest age values, so that I combine with IF function, when the formula (when copying down) bigger than 5 rows it show blanks, the rows($A$1:A1) when dragged down it show number from 1 and up, when reaching 5 it stops there then shows blanks

[roman22]

Thanks azumi

Wondering if there is an simpler way of approaching it instead of using ROWS in the formula...

Thanks for helping!

[azumi]

Thanks azumi

Wondering if there is an simpler way of approaching it instead of using ROWS in the formula...

Thanks for helping!

Using ROW function is the simplest way

[Bo_Ry]

Another method

A3
=IF(B3="","",INDEX('Capacity Data'!C:C,AGGREGATE(15,6,ROW('Capacity Data'!H$1:H$3110)/('Capacity Data'!$H$1:$H$3110=B3)/('Capacity Data'!$G$1:$G$3110=$D$3),COUNTIF(B$3:B3,B3))))

B3
=IFERROR(AGGREGATE(14,6,'Capacity Data'!$H$1:$H$3110/('Capacity Data'!$G$1:$G$3110=$D$3),ROWS(B$3:B3)),"")

[kvsrinivasamurthy]

IN A3

Please Login or Register  to view this content.

IN B3

Please Login or Register  to view this content.

Drag down both.