Using Large with multiple criteria to return a result from a corresponding cell

[mike_vr]

Howdy all,

One I've tried multiple versions and scoured the internet for but cannot find the answer...

I'm trying to return the Top 5 Destinations our employees travel to, and present them on a dashboard based on dropdown lists for the Snr Exec to choose from.

So for example, they will choose the month, department, and then based on the highest total $ amount from a pivot table and these two criteria (or more) they will see the top 3 destinations.

I've attached a workbook that had my final desperation attempt in Column I, and the array formula as follows:

Please Login or Register  to view this content.

Col. C is the destination I would like returned, Col. A contains the month, Col. B the Department, and Col. D the amounts that I'm trying to find the Top 5 from largest to smallest to return the destination from Col. C.

Any help would be massively appreciated please

Thanks,
Mike

[Glenn Kennedy]

Try this array formula:

=INDEX($C$2:$C$52,MATCH(LARGE(IF($A$2:$A$52=$G$2,IF($B$2:$B$52=$H$2,$D$2:$D$52)),ROWS(I$2:I2)),$D$2:$D$52,0))

or (if you prefer) a non-array formula... the array version is in the attached file.

=INDEX($C$2:C$52,MATCH(AGGREGATE(14,6,$D$2:$D$52/(($A$2:$A$52=$G$2)*($B$2:$B$52=$H$2)),ROWS(I$2:I2)),$D$2:$D$52,0))

[mike_vr]

Nailed it on the first go, thanks Glen!

[Glenn Kennedy]

You're welcome and thanks for the rep.