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

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

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:

Formula:
`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

2. ## Re: Using Large with multiple criteria to return a result from a corresponding cell

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))

3. ## Re: Using Large with multiple criteria to return a result from a corresponding cell

Nailed it on the first go, thanks Glen!

4. ## Re: Using Large with multiple criteria to return a result from a corresponding cell

You're welcome and thanks for the rep.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1