CUBERANKEDMEMBER Function in Excel: The n-th Member of a Set

⏱ 1 min readUpdated 5 October 2026

CubeLevel: ExpertAvailable in: Excel 2007+

In this article

Syntax
Examples
Example 1
CUBERANKEDMEMBER in practice
Where you will use it
Worked example
Mistakes people make
Related functions

CUBERANKEDMEMBER returns the n-th member of a set, e.g. the 1st, 2nd and 3rd best customers from a CUBESET: the way to build Top-N lists. Cube functions read from a Power Pivot Data Model (connection “ThisWorkbookDataModel”) or an Analysis Services cube.

Syntax

=CUBERANKEDMEMBER(connection, set_expression, rank, )

Argument	What it means
`connection`	“ThisWorkbookDataModel” for Power Pivot
`…`	Member, set or KPI expressions in MDX form

Examples

Example 1

=CUBERANKEDMEMBER(connection, set_expression, rank, )

Result: (value from your Data Model). See the worked example table below for more cases.

CUBERANKEDMEMBER in practice

Where you will use it

Custom report layouts that PivotTables can't do
Top-N lists with CUBESET + CUBERANKEDMEMBER
Finance packs from a data model

Worked example

Workbook	Formula	Result
Has a Data Model	`=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])`	(value from your Data Model)

Needs a Power Pivot Data Model, so this example is not calculated here: the result is whatever your model holds.

Mistakes people make

Mistake	What to do instead
Typos in member names	Build them with PivotTable > OLAP Tools > Convert to Formulas
No data model	#N/A or #NAME?

CUBEVALUE · CUBESET · CUBEMEMBER

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Model Free · AI can be wrong