Forum Discussion
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 return result is: 238A04, 237GW0, 237B01
My OS platform: Windows 10
Office version: 2021
Hope for your help
Thanks a lot
https://docs.google.com/spreadsheets/d/1dvhbg0AmJQk8DdjlFP7N7twue4DcKUgq/edit?usp=sharing&ouid=112310544159929632856&rtpof=true&sd=true
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)))
8 Replies
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)))
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 lotHere you go, but now it won't return the same result in your earlier example...
