Forum Discussion

ea0428's avatar
ea0428
Copper Contributor
Jul 13, 2022

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 th...
BI & Data Analysis
Formulas and Functions
office 365
ea0428's avatar
ea0428
Copper Contributor
Jul 14, 2022

SergeiBaklan that just returns me "--"

  • ea0428's avatar
    ea0428
    Copper Contributor
    Jul 14, 2022
    There are some #n/a in column AO is this what could be throwing it off?
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 14, 2022

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