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:

	A	B	c	D	E	F	G	H	I	J	K
1	APPLE	BANANA	PEACH	ORANGE	ANDY		STRAWBERRY	MELON	APPLE	PEACH
2	STRAWBERRY	MELON	APPLE	PEACH	JOHN

<tbody>
</tbody>

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.