CUBEMEMBERPROPERTY in Excel: Read Metadata from a Multidimensional Member
Retrieve a cube member property by its schema name, use a selectable property header, and understand the explicit Power Pivot Data Model restriction.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
CUBEMEMBERPROPERTY returns a property associated with a member of a multidimensional cube. It is useful for descriptive metadata beside a cube report: for example, a store’s city or a category’s administrative label, if that property exists in the model. It is not a general worksheet lookup and does not calculate a measure aggregate.
Important compatibility restriction: Microsoft explicitly says this function does not work against Excel Data Models edited in Power Pivot, because those are not multidimensional cubes. A workbook containing a Data Model is therefore not sufficient evidence that the following formulas are usable. Microsoft’s restriction
Syntax and prerequisites
=CUBEMEMBERPROPERTY(connection, member_expression, property)
All three arguments are required: an existing workbook connection name, an MDX member expression and a property name. The property can be text or a reference to a cell containing the name. Microsoft’s syntax
The property identifier comes from the cube schema. A worksheet heading that reads City does not establish that the model exposes a property with that identifier. Some models use qualified names or different metadata. Obtain the exact accepted name and member context before adapting an example; do not guess it from a report’s visible caption.
Worked example: a store’s city
Define these fictional facts solely for illustration:
TrainingCubeis an existing, usable connection to a supported multidimensional cube.[Store].[Store].&[S17]is a valid member visible to the reader.- For this member, the valid property identifier is exactly
City. - That property’s value is the text
Leeds.
The unexecuted expression is:
=CUBEMEMBERPROPERTY("TrainingCube","[Store].[Store].&[S17]","City")
Under these assumptions and successful retrieval, the expected text is Leeds. The city and store are declared toy metadata. No real store record was accessed, and no evidence establishes that a model with these names exists.
The distinction between member and property matters. The member identifies which store; the property identifies what you want to know about it. Swapping a store caption into the property argument would ask a different question and may not be meaningful. Changing the City label on a report does not rename the underlying model property.
Advanced use: selectable metadata columns
Place TrainingCube in A1, the exact store member expression in A3, and the schema property identifier City in B2. Then use:
=CUBEMEMBERPROPERTY($A$1,$A3,B$2)
This pattern is designed for a grid of members down rows and properties across columns. The connection stays fixed, the member reference changes by row, and the property reference changes by column. Before filling across, list only identifiers confirmed for the model and relevant member type.
For example, additionally stipulate that the same toy store has a valid property TradingName with text value Canal Workshop. Replacing B2 with TradingName would conditionally request Canal Workshop instead of Leeds. It would not filter transactions to that name or select a different store. Keeping a separate friendly heading above the schema-identifier row lets the report say Store name while the formula still uses TradingName.
This design makes a property-selection control possible without reconstructing the whole formula. A useful review sheet pairs every friendly label with its actual schema identifier and intended type. That prevents a later edit from quietly changing City into a different metadata field while leaving the report heading unchanged.
Property values and reporting decisions
Do not assume every property is numeric or present. The toy city is text; another model might expose a code, a date-like value or some other metadata. Preserve the returned representation until the report’s business rule explains any conversion. Displaying a store code as a number can lose meaningful leading zeros if the model actually treats it as text.
For worksheet-only source tables, XLOOKUP may answer a conventional lookup question instead. That is a different data source and contract. FORMULATEXT can help expose property formulas to reviewers. A friendly IFERROR message should not erase the difference between an unsupported model and a temporarily unavailable connection.
Failure diagnosis
Microsoft documents #NAME? for invalid stored connections or an unavailable/erroring OLAP server. Malformed member syntax or a nonexistent member produces #N/A; session-based objects can also stop resolving after PivotTable changes. Retrieval can temporarily show #GETTING_DATA…. The page does not specify a complete nonexistent-property error contract, so no particular code is asserted here for that situation. Microsoft’s remarks
First establish that the source is a supported multidimensional cube. Then verify the connection, member, property identifier and permissions in that order. If a property fails for one member but succeeds for another, compare their schema context rather than immediately changing a workbook-wide connection. Keep the original failure visible while diagnosing it; substituting an empty label can make a metadata problem look like an intentional omission.
Availability and evidence
Microsoft’s dedicated reference, checked October 18, 2026, lists Excel for Microsoft 365 and Excel 2024/2021 on Windows and Mac, Excel 2019/2016, iPad, iPhone, Android tablets/phones and Excel Web App. Those labels describe the article’s scope; they do not prove that every listed client can create or refresh every OLAP connection. Check the actual workbook, provider, permissions and client before deploying a report. No subscription entitlement beyond those product labels was established here.
First introduction and last functioning build are unknown. No Excel installation, workbook connection or server query was performed. Every model and output in this guide is hypothetical or a documented conditional expectation; no native result or screenshot is claimed.