MS Excel - Two way lookup - Index Match or sumproduct 1

[BxWill]

Have query results in the following format, beginning at cell B2.


Month----------Type----------Amt
2013-01--------Clothing------$100
2013-02--------Furniture-----$150
2013-03--------Equipment-----$250




I need to populate two tables in another worksheet that are in the following format, beginning at cell B9



Type------------2013-01------2013-02-----2013-03
Clothing
Furniture
Equipment



Type------------2012-01------2012-02-----2012-03
Clothing
Furniture
Equipment



So far, I am not able to get the following Index Match formula to work.


=index(QueryResults!B276,Match(B9,QueryResults!C3:C76,0),Match(D6,QueryResults!B3:B76,0))


Note, I do not have any range names.

Any insight as to how I can "automatically" populate the two tables using Index Match or sumproduct or another function?


Thanks in advance.

 

[SkipVought]

hi,

I'd use SUMPRODUCT in this case, as you want to do an aggregation rather than a single value.

If you're using Excel version 2007+, your querytable returns a resultset in a Structured Table. I would name the table something like [highlight]tRES[/highlight]. Otherwise, use Named Ranges in that table, based on heading names.

[tt]
C10: =SUMPRODUCT((tRES[Type]=$B10)*(tRES[Month]=C$9)*(tRES[Amt]))
[/tt]

Now a few observations. It seems that your query already does some aggregations to the year and month. That may answer an immediate question, but what about a question that might be with respect to a day or a week? I'd rather see the query return data with REAL DATES and then aggregate in the report, by month. Assumoing that you did that and your table headings were Date, Type & Amt, then your report formula woud be...

[tt]
C10: =SUMPRODUCT((tRES[Type]=$B10)*[highlight](tRES[Month]>=C$9)*(tRES[Month]<D$9)[/highlight]*(tRES[Amt]))
[/tt]
and your row of dates would have to be

[pre]
1/1/2013 2/1/2013 3/1/2013 4/1/2013 5/1/2013 6/1/2013 7/1/2013 8/1/2013 9/1/2013 10/1/2013 11/1/2013 12/1/2013 1/1/2014
[/pre]
where the last date is one period greater that you need to show for your report.

You might also consider making ONE SHEET for your report, and using a control cell to change the year. This reduced the number of sheets to maintain in your report workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}