# Index Match

Occasional Contributor

# Index Match

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

# Re: Index Match

``=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.

# Re: Index Match

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

# Re: Index Match

and first 3 largest

# Re: Index Match

unfortunately this this gives me n/a

# Re: Index Match

unfortunately this gives me an n/a

# Re: Index Match

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

# Re: Index Match

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

# Re: Index Match

@Sergei Baklan that just returns me "--"

# Re: Index Match

in column AP its a formula I've created to calculate those numbers

# Re: Index Match

There are some #n/a in column AO is this what could be throwing it off?

# Re: Index Match

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) ), "--" ) )``````