Multi-dimensional VLOOKUP / PivotTable ?

**carlyman** · 06-10-2005, 06:08 PM

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

**olasa** · 06-11-2005, 02:26 PM

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

=INDEX(E6:E37,MATCH(1,(A1=A6:A37)*(B1=B6:B37)*(C1=C6:C37)*(D1=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

**carlyman** · 06-13-2005, 11:27 AM

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

One question still: How to I incorporate a top row? See the attachment for a better explanation of the table setup.

-JC

**carlyman** · 06-13-2005, 05:00 PM

---see above for new question---

**olasa** · 06-13-2005, 05:33 PM

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

**carlyman** · 06-13-2005, 06:08 PM

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** · 06-13-2005, 07:35 PM

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...tid=3491&stc=1

Varsågod
Ola Sandström