Multi-dimensional VLOOKUP / PivotTable ?
 

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 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 |
+-------------------------------------------------------------------+

--
carlyman
------------------------------------------------------------------------
carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
View this thread: http://www.excelforum.com/showthread...hreadid=378233

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) &D6:D37,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 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378233

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

Works perfectly.

-JC


--
carlyman
------------------------------------------------------------------------
carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
View this thread: http://www.excelforum.com/showthread...hreadid=378233

---see above for new question---


--
carlyman
------------------------------------------------------------------------
carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
View this thread: http://www.excelforum.com/showthread...hreadid=378233

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 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378233

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!


--
carlyman
------------------------------------------------------------------------
carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
View this thread: http://www.excelforum.com/showthread...hreadid=378233

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 pictu
http://www.excelforum.com/attachment...tid=3491&stc=1

Varsågod
Ola Sandström


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378233