“Which product is selling best?” should be a one-second question.
In practice it usually means opening a spreadsheet, sorting a column, reading the top row, then undoing the sort before anyone notices.
SORTBY() answers it as a formula field, so the ranking updates itself and nobody has to touch the data to read it.
What SORTBY Does
SORTBY() takes a set of records, orders them by the values in one field, and lets you return values from any field in that new order. The syntax is:
SORTBY(field to sort by, ascending)
The second argument controls direction. FALSE() sorts from highest to lowest, which is what rankings need. TRUE() sorts lowest to highest.
How it differs from SORT
SORT() only orders a single list of values by themselves. SORTBY() works across a collection of records and, crucially, lets you sort by one field while returning another. That difference is the whole point: you want to order products by sales quantity but read back the product name.
The NTH pairing
SORTBY() hands you an ordered list. To pull one item out of it you wrap the whole thing in NTH(), which returns the value at a given position. Position 1 is the top of the ranking, position 2 the runner-up, and so on. That pairing is what turns a sorted list into a leaderboard.
The Scenario
Marcus runs TechGear Penang, a small electronics and accessories shop in George Town. Between receiving stock, selling and reordering he has no time to interrogate a spreadsheet daily, but he does need to know which products are moving and which are actually making money.
He built one Product Sales table and let formula fields answer both questions.
The Tables
Product Sales
- Product Name: Identifies each item sold.
- Sales Quantity: Units sold in the period.
- Unit Price: Selling price per unit in ringgit.
- Month: The period the figures belong to.
- Revenue: Sales Quantity multiplied by Unit Price.
- Stock left and Initial Stock: Remaining and opening stock.
- Product Image: A visual reference.
Summary
A second table holds the rankings. Its only input is a numbering field.
- No: The ranking position, 1 to 10.
- Best Selling Product: The product name at that position by units sold.
- Sales Quantity: The figure at that position.
- Top 3 Best Selling: Name and quantity combined, for the top three only.
- Top 3 Highest Revenue: The same idea ranked by money instead.
Tip: The No field is doing the heavy lifting. Because every formula reads its position from that column, one formula written once fills the entire ranking down the table.
Building the Ranking
Returning the product name
NTH(Product Sales.SORTBY(Product Sales.Sales Quantity, FALSE()).Product Name, No)
Reading it in four parts:
- Product Sales.Sales Quantity is the field to sort by.
- SORTBY(…, FALSE()) orders the records from highest to lowest.
- .Product Name pulls the names out of that sorted result.
- NTH(…, No) returns the one at the position in the No field.
Where No is 1, the result is Laptop Stand at 620 units.
Returning the figure
NTH(Product Sales.SORTBY(Product Sales.Sales Quantity, FALSE()).Sales Quantity, No)
Identical apart from the field being returned. Where No is 1, the result is 620.
Note: The sort field and the return field are set independently. That is what lets one formula give you the name and another the number, both from the same ranking.
Limiting It to a Top Three
A full ten-row ranking is often more than anyone needs. Wrapping the formula in an IF condition caps it at three and joins the name to the figure.
IF(No <= 3, NTH(Product Sales.SORTBY(Product Sales.Sales Quantity, FALSE()).Product Name, No) & ” – ” & NTH(Product Sales.SORTBY(Product Sales.Sales Quantity, FALSE()).Sales Quantity, No), “”)
Rows 4 to 10 return an empty string, so the column simply stops after three entries.
Ranking by Revenue Instead
Same structure, different sort field.
IF(No <= 3, NTH(Product Sales.SORTBY(Product Sales.Revenue, FALSE()).Product Name, No) & ” – RM” & NTH(Product Sales.SORTBY(Product Sales.Revenue, FALSE()).Revenue, No), “”)
Tip: The revenue figures come back unformatted, as RM164725 rather than RM164,725.00. Wrap the NTH() result in TEXT() with a format such as “0.00” if you want proper currency presentation.
Why Two Rankings Are Worth Building
Compare the two columns and the reason for the exercise becomes obvious.
| Product | Rank by units | Rank by revenue |
|---|---|---|
| Laptop Stand (RM19) | 1st, 620 units | 8th, RM11,780 |
| Bluetooth Earbuds (RM599) | 8th, 275 units | 1st, RM164,725 |
Marcus’s best-selling product by volume is nearly his worst earner, and his biggest earner barely registers on the units leaderboard. Ranking by one measure alone would send him reordering the wrong stock.
Important: This is the practical argument for building both columns rather than picking one. “Best selling” is ambiguous until you say whether you mean units or money, and for most product ranges the answers differ.
Practical Use Cases for SMEs and Startups
- Product leaderboards: Top sellers by units and by revenue, side by side and always current.
- Sales team rankings: Order representatives by closed value and return their names for a dashboard.
- Reorder priorities: Sort by stock remaining ascending to surface what runs out first.
- Client value reporting: Rank customers by lifetime spend and show the top five on a summary page.
- Slowest movers: Sort ascending instead of descending to find the stock that is not shifting.
Frequently Asked Questions (FAQ)
What does the SORTBY function do in Lark Base?
It orders a set of records by the values in one field, then lets you return values from any field in that new order. Combined with NTH() it produces rankings that update automatically.
What is the difference between SORT and SORTBY in Lark Base?
SORT() orders a single list of values by themselves. SORTBY() works across records and lets you sort by one field while returning another, which is what rankings and leaderboards require.
How do I sort from highest to lowest?
Pass FALSE() as the second argument to SORTBY(). Use TRUE() for lowest to highest, which is useful for finding slow movers or low stock.
How do I return only the top three results?
Wrap the formula in an IF condition testing the ranking position, such as IF(No <= 3, …). Positions beyond three return an empty string, so the column stops after the third entry.
Why does my ranking show the same product in every row?
NTH() needs a position that changes per row. If every row returns the top result, the position argument is probably a fixed number rather than a field such as No that increments down the table.
Can I combine the product name and its figure in one field?
Yes. Join two NTH() results with the ampersand operator and a separator, for example the name, then ” – “, then the quantity.
How do I format a currency value returned by SORTBY?
Wrap the NTH() result in TEXT() with a numeric format such as “0.00”, then join a currency prefix with the ampersand operator. Without it the figure appears unformatted.
Letting the Ranking Maintain Itself
SORTBY() replaces a habit rather than a formula.
Instead of sorting a column to answer a question and then putting it back, the answer sits in a field and stays right.
Build the ranking once, decide honestly which measure you are ranking by, and consider building the second measure too.
Ready to Power Your Business with Lark?
Lark Base is just one part of an all-in-one platform that brings messaging, meetings, documents, approvals, and automations together for your entire team.
As the Platinum Partner for Lark in Malaysia, Exabytes offers tailored Lark plans, hands-on onboarding, and dedicated local support to help your team get answers from their data without the manual work.
