Complicated Index/Match formula

[SSG QuarterMaster]

I am trying to create a final data spread sheet that is automatically filled
in from a raw data page, where the raw data is imported from a dicoverer
query. below is an example of my data.

Raw Data: Sheet 1 - Cells A5:E13
Shop TSD 1-May-09 4-May-09 5-May-09
04 PCKE
04 PJK3
04 Shop Total
18 BOKB 1.00 1.00 2.00
18 Shop Total 1.00 1.00 2.00
19 JMMX 2.13 1.00
19 Shop Total 2.13 1.00
24 JTKT 1.00 1.00 1.00

This sheet will only have data for certain days.
---------------------------
Final Data: Sheet 2: cells A3:I15
Shop TSD 1-May Subtotal AVE 5/1 2-May 3-May 4-May
04 PCKE C4 0.00 #DIV/0!
04 PJK3 0.00 #DIV/0!
04 Shop Total 0.00 #DIV/0!
18 BOKB 0.00 #DIV/0!
18 Shop Total 0.00 #DIV/0!
19 JMMX 0.00 #DIV/0!
19 Shop Total 0.00 #DIV/0!
24 JTKT 0.00 #DIV/0!
24 Shop Total 0.00 #DIV/0!
30 PCKJ 0.00 #DIV/0!
30 PJK1 0.00 #DIV/0!
30 PJKM 0.00 #DIV/0!

Sheet 2 subtotals and Averages are formulas I alreday have input. This sheet
will show everyday with a week designated as Saturday thru Friday. What I
need to do is populate the columns that have dates for the column headers
with the appropriate data from the raw data sheet. Example would be C4 would
be replaced by data based on Fnal Data: Column A & B (04PCKE) and cell C3
(date) being looked up on the raw data sheet for the matching Column A & B
and the correct date.

Any help with this would be greatly appreciated

 

[smartin]

I am trying to create a final data spread sheet that is automatically filled
in from a raw data page, where the raw data is imported from a dicoverer
query. below is an example of my data.

Raw Data: Sheet 1 - Cells A5:E13
Shop TSD 1-May-09 4-May-09 5-May-09
04 PCKE
04 PJK3
04 Shop Total
18 BOKB 1.00 1.00 2.00
18 Shop Total 1.00 1.00 2.00
19 JMMX 2.13 1.00
19 Shop Total 2.13 1.00
24 JTKT 1.00 1.00 1.00

This sheet will only have data for certain days.
---------------------------
Final Data: Sheet 2: cells A3:I15
Shop TSD 1-May Subtotal AVE 5/1 2-May 3-May 4-May
04 PCKE C4 0.00 #DIV/0!
04 PJK3 0.00 #DIV/0!
04 Shop Total 0.00 #DIV/0!
18 BOKB 0.00 #DIV/0!
18 Shop Total 0.00 #DIV/0!
19 JMMX 0.00 #DIV/0!
19 Shop Total 0.00 #DIV/0!
24 JTKT 0.00 #DIV/0!
24 Shop Total 0.00 #DIV/0!
30 PCKJ 0.00 #DIV/0!
30 PJK1 0.00 #DIV/0!
30 PJKM 0.00 #DIV/0!

Sheet 2 subtotals and Averages are formulas I alreday have input. This sheet
will show everyday with a week designated as Saturday thru Friday. What I
need to do is populate the columns that have dates for the column headers
with the appropriate data from the raw data sheet. Example would be C4 would
be replaced by data based on Fnal Data: Column A & B (04PCKE) and cell C3
(date) being looked up on the raw data sheet for the matching Column A & B
and the correct date.

Any help with this would be greatly appreciated

You can use INDEX/MATCH for multiple category lookups but when the
target data is numeric I find SUMPRODUCT is much easier. The technique
is described here:

http://xldynamic.com/source/xld.SUMPRODUCT.html

In either method with appropriate use of absolute row & column
references you should be able to write a single formula that can be
filled through entire region.

 

[SSG QuarterMaster]

I am not looking to sum the numbers I need to transfer them to sheet 2 in the
appropriate cell based on the left two most columns and the row geading.

 

[smartin]

My bad. SUMPRODUCT /can/ be used to do a multiple key lookup (not just
to obtain a sum), but it will not work in your two dimensional layout.

Try this array formula* in Sheet2 F2 and fill right and down as needed:

=INDEX(Sheet1!$C$2:$E$9,MATCH(1,($A2=Sheet1!$A$2:$A$9)*($B2=Sheet1!$B$2:$B$9),0),MATCH(F$1,Sheet1!$C$1:$E$1,0))

*Commit array formula by pressing Ctrl+Shift+Enter (not just Enter).

Note the two different usages of MATCH. The first is good for multiple
keys, as you have two keys in rows. The second is simpler when only one
key needs to be matched, as you have one key in columns.

 

[SSG QuarterMaster]

That was just the fix I needed. Thank you.

My bad. SUMPRODUCT /can/ be used to do a multiple key lookup (not just
to obtain a sum), but it will not work in your two dimensional layout.

Try this array formula* in Sheet2 F2 and fill right and down as needed:

=INDEX(Sheet1!$C$2:$E$9,MATCH(1,($A2=Sheet1!$A$2:$A$9)*($B2=Sheet1!$B$2:$B$9),0),MATCH(F$1,Sheet1!$C$1:$E$1,0))

*Commit array formula by pressing Ctrl+Shift+Enter (not just Enter).

Note the two different usages of MATCH. The first is good for multiple
keys, as you have two keys in rows. The second is simpler when only one
key needs to be matched, as you have one key in columns.