Jul 13 2022 11:58 AM
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.
Jul 13 2022 12:07 PM
=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.
Jul 13 2022 12:16 PM
Jul 13 2022 01:18 PM
Perhaps you may drop small sample file or at least screenshot?
Jul 13 2022 01:26 PM
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
Jul 14 2022 07:27 AM
Jul 14 2022 07:33 AM
Jul 14 2022 12:56 PM - edited Jul 14 2022 12:58 PM
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) ), "--" ) )