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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Model[/TD]
[TD]INPUT NO1 (MODEL01 or MODEL02 or.....)[/TD]
[/TR]
[TR]
[TD]Unit[/TD]
[TD]INPUT NO2 (UNIT01 or UNIT02 or.....)[/TD]
[/TR]
[TR]
[TD]Minimum[/TD]
[TD]OUTPUT or VALUE TO BE SHOWN FROM TABLE 01[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD]OUTPUT or VALUE TO BE SHOWN FROM TABLE 02[/TD]
[/TR]
</tbody>[/TABLE]







[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD="align: center"]TABLE 01[/TD]
[TD="align: center"]UNIT01[/TD]
[TD="align: center"]UNIT02[/TD]
[TD="align: center"]UNIT03[/TD]
[TD="align: center"]UNIT04[/TD]
[/TR]
[TR]
[TD]MODEL01[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[/TR]
[TR]
[TD]MODEL02[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[/TR]
[TR]
[TD]MODEL03[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[/TR]
[TR]
[TD]MODEL04[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[/TR]
[TR]
[TD]MODEL05[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[TD]=MIN(ARRAY)[/TD]
[/TR]
</tbody>[/TABLE]









[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD="align: center"]TABLE 02[/TD]
[TD="align: center"]UNIT01[/TD]
[TD="align: center"]UNIT02[/TD]
[TD="align: center"]UNIT03[/TD]
[TD="align: center"]UNIT04[/TD]
[/TR]
[TR]
[TD]MODEL01[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[/TR]
[TR]
[TD]MODEL02[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[/TR]
[TR]
[TD]MODEL03[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[/TR]
[TR]
[TD]MODEL04[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[/TR]
[TR]
[TD]MODEL05[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[TD]=MAX(ARRAY)[/TD]
[/TR]
</tbody>[/TABLE]











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".