vlookup with OLAP CUBE

[qontrol]

Hi everyone,

So here is the thing...

I have 1 sheet which has several rows of data (article number e.g. 1000000, 1000001, 1000002,...) with some information next to it and I also have an OLAP running on another sheet with net values, gross margin etc per article number. How can i lookup the values from the OLAP cube to the other sheet? Vlookup does not work (gives an N/A error).

Thank you.

Regards
Qontrol

 

[SpillerBD]

Just on a few assumption, but using INDEX(MATCH) should be your primary lookup method. You say OLAP... is that feeding some kind of PivotTable or a Table?
In either case, you might need to have the ranges exceed the table range.
Another possibility can be data mismatch... Text vs Numbers. Make sure your lookup values are the same type as in the lookup range.

 

[qontrol]

Yes, the OLAP is feeding a PivotTable with also articlenumbers and their values. Actually the values in the pivottable are the values that I need. I need to lookup the articlenumbers in the PivotTable and return the other data.

So.. Index/Match should work also with a pivottable?

 

[SpillerBD]

yes. Make sure your Pivot Table begins at B2. Then when you reference the ranges, use Row 1 as the beginning point.
I forget how the GETPIVOTDATA function works out with OLAP (its been a while since I had OLAP) but that could be a possibility instead of using a lookup.
GETPIVOTDATA is easy if you've done SQL or Table referencing.

Also, OLAP limitations led me to building a PivotTable from a PivotTable.... Which is not necessarily an obvious solution, but gets around OLAP limitations

 

[qontrol]

Okay, I've got it too work with GETPIVOTDATA. There was something wrong with my references, but ["&A1&"] made it work!

Thank you very much!

 

[SpillerBD]

Congratulations!
I have had a couple of projects where the PivotTable was just the easiest tool to get the summary. And (at first) stumbled thru the GETPIVOTDATA.
Those projects, no one looked at the PivotTable at all !