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

HansVogelaar's avatar
HansVogelaar
MVP
Jun 17, 2022

littlevillage 

That is a rather unhelpful sample workbook since the three largest values are the ONLY values.

You can use the formula

 

=TEXTJOIN(",",TRUE,FILTER(B2:B5&C2:C5,A2:A5>=LARGE(UNIQUE(A2:A5),3)))

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

    Hi HansVogelaar,
    Thanks for your response
    You have sent me a great formula for this case. I did it well for the sample file. But have something with full file that has more than the same 3 largest values. In this example is three number 1. The formula returns 5 values (237E51, 238GW0, 238A04, 237W01, 237B01). However I just want to receive 3 values with the 3 largest values in column C (1, 0.5, 0.33)

    My formula: 

     

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

     

    I have added an image and the link of full file below

    https://docs.google.com/spreadsheets/d/1nzBuLgDSLXrR911oGqD48UjxXZTVjliu/edit?usp=sharing&ouid=112310544159929632856&rtpof=true&sd=true 

    Hope for your response
    Thanks a lot

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

        HansVogelaar 

        The formula doesn't return the three values in column D and E, if there are more than three number 1 in column C , I have added 4 number 1 in column C then the formula return is 4 values.

         

        the link of full file below

        https://docs.google.com/spreadsheets/d/1nzBuLgDSLXrR911oGqD48UjxXZTVjliu/edit?usp=sharing&ouid=112310544159929632856&rtpof=true&sd=true 

Resources