LARGE function within an index Match (No duplicates)

[jamescooper]

Hello,

I have the following formula, with in column B, range (B11:B35), the rank 1 to 25, Cell B11 =1.

As I drag it down I find duplicates, is there a way of modifying the formula below so it doesn't pick up duplicates?

=TEXT(INDEX(Pricing!$D$10:$D$493,MATCH(LARGE(Pricing!$Q$10:$Q$493,B11),Pricing!$Q$10:$Q$493,0)),"dd/mm/yyyy")

Many thanks.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jamescooper]

Thanks Fluff - Microsoft 365

 

[Fluff]

Thanks for that, how about

=TEXT(TAKE(SORTBY(Pricing!$D$10:$D$493,Pricing!$Q$10:$Q$493,-1),25),"dd/mm/yyyy")

 

[kvsrinivasamurthy]

This is an ARRAY formula. Needs Ctrl+Shift+Enter together, not simple Enter.

=TEXT(INDEX(Pricing!$D$10:$D$493,MATCH(LARGE(Pricing!$Q$10:$Q$493-(ROW($Q$10:$Q$493)*10^-6),B11),Pricing!$Q$10:$Q$493-(ROW($Q$10:$Q$493)*10^-6),0)),"dd/mm/yyyy")

 

[Fluff]

This is an ARRAY formula. Needs Ctrl+Shift+Enter together, not simple Enter.

As the OP is using 365, then it does not need CSE entry.

 

[jamescooper]

Thanks, I get #SPILL! error.

 

[Fluff]

Are you putting the formula into a table, or just a normal range?
If it's a range, clear all the cells below the formula.