Forum Discussion

littlevillage's avatar
littlevillage
Iron Contributor
Jun 17, 2022
Solved

How can formula return the 3 largest values

Hi @All, How can formula returns the first 3 values in column B and C from the 3 largest values in column A. In this example: the 3 largest values in Column A: 0.50, 1.00, 1.00, 0.33. The expected...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 20, 2022

    littlevillage 

    How about

    =TEXTJOIN(", ",TRUE,INDEX(FILTER(D2:D16&E2:E16,(A2:A16="238001")*(B2:B16="Thất lạc tại Bưu cục
    网点到件遗失")*(C2:C16>=LARGE(C2:C16,3)),""),SEQUENCE(3)))

littlevillage's avatar
littlevillage
Iron Contributor
Jun 20, 2022

HansVogelaar 

For this case, I expect result is whatever 3 values of four return values, such as 237Q52, 238A04, 237W01 for 3 values respectively (1, 1, 1) or 237Q52, 237E51, 237GW0 for 3 values respectively (1, 0.5, 0.33)...etc,

HansVogelaar's avatar
HansVogelaar
MVP
Jun 20, 2022

littlevillage 

How about

=TEXTJOIN(", ",TRUE,INDEX(FILTER(D2:D16&E2:E16,(A2:A16="238001")*(B2:B16="Thất lạc tại Bưu cục
网点到件遗失")*(C2:C16>=LARGE(C2:C16,3)),""),SEQUENCE(3)))

  • littlevillage's avatar
    littlevillage
    Iron Contributor
    Jun 21, 2022

    Hi HansVogelaar 

    For the earlier answer, the formula returns 3 values for the count of number 1 in column C ❤️

    For the last answer, the formula returns 3 values for the count of number 1 in column C >=3

    So you have given me two cases

    Below, It is the formula cover 2 cases:

     

    =IF(COUNTIFS(C2:C16,"=1",A2:A16,"=238001",B2:B16,"=Thất lạc tại Bưu cục
    网点到件遗失")>=3,TEXTJOIN(", ",TRUE,INDEX(FILTER(D2:D16&E2:E16,(A2:A16="238001")*(B2:B16="Thất lạc tại Bưu cục
    网点到件遗失")*(C2:C16>=LARGE(C2:C16,3)),""),SEQUENCE(3))),TEXTJOIN(", ",TRUE,FILTER(D2:D16&E2:E16,(A2:A16="238001")*(B2:B16="Thất lạc tại Bưu cục
    网点到件遗失")*(C2:C16>=LARGE(UNIQUE(C2:C16),3)),"")))

     

    It is worked to cover all cases

    I just want to say thank you so much
    You are not only a expert, also you pay time for me
    Kind regards,

    small_village

Resources