Multi-dimensional VLOOKUP / PivotTable ?

[carlyman]

I am working with multi-dimensional data storage and want to lookup
values in a pivot table. See attachment for basic setup of my table.

I need a way to get the value at (a1->b2->c1@d6)...which would return
41.

Is there a way to do this using pivot tables and vlookup, or do I need
to be using other functions? The data schema can be modified if
needed.

Thanks,
JC


+-------------------------------------------------------------------+
|Filename: example.gif |
|Download: http://www.excelforum.com/attachment.php?postid=3476 |
+-------------------------------------------------------------------+

 

[olasa]

The easiest would be to use the original table (input to the
Pivottable)

=INDEX(E6:E37,MATCH(A1&CHAR(1)&B1&CHAR(1)&C1&CHAR(1)&D1,A6:A37&CHAR(1)&B6:B37&CHAR(1)&C6:C37&CHAR(1)&D637,0))
Confirm the formula by holding down Ctrl and Shift, then hit Enter.

See enclosed zip-file

Hope it helped
Ola Sandström


+-------------------------------------------------------------------+
|Filename: Book4.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3478 |
+-------------------------------------------------------------------+

 

[carlyman]

Fantastic! I always wondered how to use the {} where you have to push
ctrl-shift-enter.

Works perfectly.

-JC

 

[carlyman]

---see above for new question---

 

[olasa]

If you want to make a Table layout as the one on the right; insert this
formula (also see encl.picture):
=SUMPRODUCT(($I2=$B$6:$B$37)*($J2=$C$6:$C$37)*($K2=$D$6:$D$37)*(L$1=$E$6:$E$37)*$F$6:$F$37)

HTH
Ola Sandström


+-------------------------------------------------------------------+
|Filename: Clipboard01.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3489 |
+-------------------------------------------------------------------+

 

[carlyman]

Ola, thanks for all your help; however, I think I mistated my question.

I do not want to transform the data into a new layout with a "top row."
The way the data is stored originally has the top row.

So, in theory, I want to say: level1->level2->level3@TOP1 ==> <value>
Where TOP1 is a column header, and the levels are row categories.

See the right-side table in my last attachment to see how the data is
originally stored.

Tack!

 

[olasa]

Ok. I thought the left table was the input table, and the right a
summary table.
So, the right table is the input table, the left table is invalid and
the previous Selection is the same.

Here is the formula adjusted to the right input table:
=SUMPRODUCT(($A2=$A$5:$A$8)*($B2=$B$5:$B$8)*($C2=$C$5:$C$8)*(D$2=$D$4:$K$4)*$D$5:$K$8)

See the update picture:
http://www.excelforum.com/attachment.php?attachmentid=3491&stc=1

Varsågod
Ola Sandström


+-------------------------------------------------------------------+
|Filename: Clipboard01.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3491 |
+-------------------------------------------------------------------+