VLOOKUP from a Pivot table that has reff value (example MIN/MAX).

[Mostafizul]

Hello Legends,
First of all a Huge Thanks for looking into my issue. So I am kinda new to this VLOOKUP, INDEX, MATCHING etc. So far I have been solving issues with other basic functions. But I guess this time I will have to push my boundary a little bit further. I already tried to learn about VLOOKUP, INDEXING etc from various resources available in the internet but found out that the issue I am facing right now can't (more or less) be solved with VLOOKUP (I really hope I am wrong here). Because I am trying to pick values from a Pivot Table and the data inside the pivot table is MIN/MAX values from another table or reff. Below are the tables for your reff:

Model	INPUT NO1 (MODEL01 or MODEL02 or.....)
Unit	INPUT NO2 (UNIT01 or UNIT02 or.....)
Minimum	OUTPUT or VALUE TO BE SHOWN FROM TABLE 01
Max	OUTPUT or VALUE TO BE SHOWN FROM TABLE 02

<tbody>
</tbody>

TABLE 01	UNIT01	UNIT02	UNIT03	UNIT04
MODEL01	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)
MODEL02	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)
MODEL03	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)
MODEL04	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)
MODEL05	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)	=MIN(ARRAY)

<tbody>
</tbody>

TABLE 02	UNIT01	UNIT02	UNIT03	UNIT04
MODEL01	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)
MODEL02	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)
MODEL03	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)
MODEL04	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)
MODEL05	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)	=MAX(ARRAY)

<tbody>
</tbody>

Hope this helps understanding my situation. Any help solving this would be much appreciated. Thanks a lot in Advance!


Best Regards,
~ M

 

[MarcelBeug]

If I understand your requirements correctly, GETPIVOTDATA is the function you are looking for.

The easiest way to get a head start with GETPIVOTDATA, is to select some empty cell, type = and then select a field in your Pivot table and press enter.
Now the cell has the GETPIVOTDATA function.

If not, check your Excel options - Formulas, and make sure to have checked option "Use GetPivotData functions for PivotTable references".