Index Match

Copper Contributor

I need to find a way to find a way to use index match or another lookup function to pull the 1st, 2nd, 3rd, 4th, 5th largest number from a data set based on a criteria. For example, I want to find the 2nd largest stock price if the asset class is equity. There are multiple asset classes so there is a column that contains whether the security is in equity, fixed income, etc. Thinking I need to use the LARGE function nested within an INDEX MATCH. Please help, I'm begging. 

12 Replies

@ea0428 

=LARGE(IF($C$2:$C$25=H$4,$D$2:$D$25),$F5)

You can try this formula for the dataset of the attached example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

large.JPG 

@ea0428 

Since you are on Excel 365, for such sample second largest for "B" could be

image.png

@ea0428 

and first 3 largest

image.png

unfortunately this this gives me n/a
unfortunately this gives me an n/a

@ea0428 

Perhaps you may drop small sample file or at least screenshot?

@Sergei Baklan 

ea0428_0-1657743888654.png

So I want to find the top 5 largest numbers in column AP, given that it has the criteria of "Fixed Income" in column AO

@ea0428 

Please check in attached file

image.png

@Sergei Baklan that just returns me "--"

in column AP its a formula I've created to calculate those numbers
There are some #n/a in column AO is this what could be throwing it off?

@ea0428 

If so - yes. You may clean it like

 

=LET(
   class, IFERROR( AO2:AO100, ""),
   value, IFERROR( AP2:AP100, -1E+40),
   IFERROR( LARGE( FILTER(value, class="Fixed income"), SEQUENCE(5) ), "--" ) )