Large Formula with Duplicate Values

Hi,

I have a list of six loans and some contain duplicate amounts. Column A is the Loan Name and Column B is the amount of the loan:

Loan XYZ, $300
Loan ABC, $300
Loan MNO, $200
Loan PQR, $250
Loan HIJ, $250
Loan 123, $100

How would I display the top five loans with its corresponding loan amount, regardless of the duplicate values, with an array formula? Would I have to assign, in Column C, a unique value for each row?

I used the following formulas to pull the loan amounts:
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+0)
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+1)
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+2)
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+3)
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+4)

I used the following formula to pull the loan name:
=INDEX($A$1:$A$6,MATCH([Dollar Amount of Loan],$B$1:$B$6,0))

The result I keep getting, which I don't want, is this:
Loan XYZ, $300
Loan XYZ, $300
Loan PQR, $250
Loan PQR, $250
Loan MNO, $200

My intended result would look like this:
Loan XYZ, $300
Loan ABC, $300
Loan PQR, $250
Loan HIJ, $250
Loan MNO, $200

I hope this is clear and apologize if it's confusing. Any help would be so appreciated.


Thanks,

Originally Posted by benishiryo

hi triv, it would be much better if you can upload a sample Excel file next time. see if the file here helps.

to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

Thank you, benishiryo. I will keep that suggestion in mind next time. I do apologize if I was a bit confusing. I'll try your solution tonight and see how it goes. thanks again for the reply.

Originally Posted by :) Sixthsense :)

In D1 cell

Formula:

Please Login or Register  to view this content.

In E1 cell

Formula:

Please Login or Register  to view this content.

Drag both the formula's down....

Thanks, Sixthsense. I'll also try your solution.


This forum is awesome.