sumproduct matrix with two row lookups

  • Thread starter Thread starter jpwolf00
  • Start date Start date
J

jpwolf00

I am hoping its possible to build a function that will allow me to loo
through a matrix and find a data point. I am currently using
combination of sumproduct formulas to find a reference in a column an
row and then return the data point. I would like to be able to seac
in a column for a row heading, then search the rows immediately belo
that point.

Here is an example. I would like to be able to gather the data fro
this page to insert into another sheet split into regions.

'Example Image
(http://server5.theimagehosting.com/image.php?img=excelexample.gif
 
jp,

Your table is a "multiple record type" table. I don't think it's considered
a matrix, atall. You have header records (East, West...), and detail
records (Apples, Oranges...). Excel doesn't have any tools for that.
Someone may write a macro specific for you, or stay up late making formulas
to use in cells -- I don't have enough coffee for that, I fear.

What you need is to get the table into a bona-fide database table layout,
something like:

East | Apples | 36 | 62 | 39 ...
East | Oranges | 83 | 87 | 79 ...
..
..
..
West | Apples | 53 | 46 | 62 ...
..
..

This layout is easily summarized, looked-up, and all kinds of things with
Excel tools. You could then reduce the table to just West, for example,
with an Autofilter. Very nice. But it doesn't give you the nice report
layout (as in your picture), with the headings for East, West, etc. You
might consider using Access for this project. It does that kind of stuff
in it's sleep, with very little effort you your part.
 
Back
Top