CUBERANKEDMEMBER Function
Returns the member at a given position in a set — perfect for top-N lists.
Category
Cube
Available in
Excel 2007+ for Windows, Microsoft 365
Returns
The nth member of a set
Overview
CUBERANKEDMEMBER picks the 1st, 2nd, 3rd… member from a set. With a set sorted by sales, that's a live Top 5 that re-ranks itself every time the data refreshes.
Point CUBEVALUE at each ranked member to show its number beside it.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| connection | Yes | The connection name in quotes. For the workbook's own Data Model it's "ThisWorkbookDataModel"; for an Analysis Services cube, use the name shown in Data › Queries & Connections. |
| set_expression | Yes | Usually a cell holding a CUBESET, or set text such as "{[Sales].[Region].&[North],[Sales].[Region].&[South]}". |
| rank | Yes | Which position to return: 1 is the first member of the set. |
| caption | Optional | Text to show instead of the member's name. |
Examples
Example 1 — Top 3 products
B1 holds a CUBESET of products sorted by Total Sales (descending). Column A holds the rank numbers.
| A | B | C | |
|---|---|---|---|
| 1 | Set | Products | |
| 2 | Rank | Product | Total Sales |
| 3 | 1 | Toner | 182,400 |
| 4 | 2 | Paper | 141,950 |
| 5 | 3 | Pens | 96,300 |
Column C uses =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]",B3).
Common mistakes
- Errors or empty cells at the bottom of the list. The rank is larger than the number of members. Check with CUBESETCOUNT first.
Notes
- Cube functions talk to a live Data Model or Analysis Services cube, so they can't run in our practice Sandbox.
- Related functions: CUBEVALUE, CUBEMEMBER, CUBESET, CUBERANKEDMEMBER, CUBESETCOUNT, CUBEMEMBERPROPERTY, CUBEKPIMEMBER, GETPIVOTDATA.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.