Get the top 5 values and associated names

Hi everyone,

I have a table as below:

A B C
Revenue [USD] Status Name
-----------------------------------------
$23,250.00 Pending Jack
$18,600.00 Won Jane
$9,300.00 Pending Joe
$9,300.00 Pending James
$7,750.00 Pending Mike
$3,990.00 Won Bob
$3,990.00 Lost Peter
$3,325.00 Lost Mark
$3,250.00 Won Mike
$3,000.00 Won Jones
$2,660.00 Lost Sarah
$2,660.00 Lost Cat
$2,325.00 Won Dean
$1,995.00 Lost George
$1,995.00 Pending Lily
$1,550.00 Won Leona
$1,445.00 Won Sam
$1,330.00 Lost Sam
$1,330.00 Lost Dean
$1,000.00 Won Jane


Now i want to get 1)top 5 Amounts that are pending 2)along with the name associated with it.

I have successfully achieved the task 1 of getting the top 5 pending tasks using the following formula : {=LARGE(IF(B:B="Pending",A:A),ROWS($1:1))}

Now, coming to the second one, I have used this =INDEX(C:C,MATCH(J26,A:A,0))

But Amount $9,300 is shown twice, So i need to get two names there.

Problem.xlsx

Would appreciate your help.

Thanks in advance.

Try this :

Dear TheCman81,

Your formula works brilliantly on the excel sheet that is attached. But 1 problem.

When you precede a value in the table, it displays its name irrespective to its pending or won.
For ex: Row 5 has the same amount as row 16. but Row 5 is "Won", So its name should not be displayed.

Also, i was wondering do we have to mention the data range exact to the data ? can i not give C:C instead of $C$2:$C$22.
Because i am creating a table that reference to various sheets and based on the sheet name, it extracts the data. and every sheet has different number of rows.

Attached both the sheets
Deals data.xlsxProblem(1) (1).xlsx


Thanks

Thanks Sunflower, Can you please explain how you have done this.

Cheers

Oh Sorry, Just saw this.. Will try it and get back to you. thanks for you response.