Cube Value Lookup

[deharris]

I have an Excel 2016 PowerPivot data model where I'm trying to find a member value based on whether 4 other member values exist. A sort if "if" statement. If Values A, B, C & D exist, bring me back value E. Value E can be 1 of three things based on values A-D being found. I've researched CUBEMEMBERPROPERTY but it doesn't seem to work with PowerPivot. To be clear, I'm looking for a label, not a number. I'm new to all the syntax for the more detail the better.

"Cube Formula"
"ThisWorkbookDataModel"
Values A-E all come from the same table.

thank you for any help.

 

[Legacy 143009]

I think This is what you need:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]c[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]APPLE[/TD]
[TD]BANANA[/TD]
[TD]PEACH[/TD]
[TD]ORANGE[/TD]
[TD]ANDY[/TD]
[TD][/TD]
[TD]STRAWBERRY[/TD]
[TD]MELON[/TD]
[TD]APPLE[/TD]
[TD]PEACH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]STRAWBERRY[/TD]
[TD]MELON[/TD]
[TD]APPLE[/TD]
[TD]PEACH[/TD]
[TD]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Into K1 cell: {=INDIRECT("E"&MATCH(G1&H1&I1&J1,A:A&B:B&C:C&D:D,0))}
This will output name "JOHN" in column E based on the values in A, B, C and D columns.

It's an array formula so you have to Ctrl+Shift+Enter after paste.

 

[deharris]

thank you for your reply. I believe your solution is an Excel solution and I'm looking for a way to write this as a Cube formula to retrieve data out of a power pivot data model.