How to find values 3 dimensional in excel

[Shanzil]

Hi,
How can i look values in 3D by using excel formula. I two tables in tow different sheets.(stock table and output table) Output table having location and product name and Output table having location, product name and stock on hand. I want to lookup stock values from stock table values by looking location and product name in stock on hand col. I do this this by using Vlookup and index but i have to change search col every time. Plz help.

 

[bosco_yip]

Try,

In "Output table" sheet C2, formula copied down :

=IFERROR(1/(1/VLOOKUP(B2,Stock_table!A$2:D$12,MATCH(A2,Stock_table!A$1:D$1,0),0)),"")

Regards
Bosco

 

[Shanzil]

Can i apply this directly to lookup values form pivot. Doesn't work with pivot table. i have make separate tables.

 

[bosco_yip]

Can i apply this directly to lookup values form pivot. Doesn't work with pivot table. i have make separate tables.

If the source table change to "Pivot" sheet, then the formula become:

=IFERROR(1/(1/VLOOKUP(B2,Pivot!$A$5:$AN$15,MATCH(A2,Pivot!$A$4:$AN$4,0),0)),"")

Regards
Bosco

 

[Peter Bartholomew]

Since you have the data you need in the pivot table, why not
= GETPIVOTDATA("Restantall",Pivot!$A$3,"Varenr.",Product,"Lokasjonskode",Location)
where 'Product' and 'Location' refer to columns on the sheet 'Output Table' either as whole column references or relative references.