Advanced Lookups

[Guest]

I'm not too sure if this would be a formula or VB thing. I am making a
Yearly sales report/forecast. What I need to do is look up each part number
and pull the qty from each but only for specific customer (EX: Qty for p/n
1-1 but only for Cust b)
Tried vlookup but stops first time it hits the p/n. I think it would be
SUMPRODUCT or SUMIF but I'm not sure how that would be written. Thanks

Monthly sheet layout
Cust P/N Qty
a 1-1 4
a 1-2 3
b 1-1 8
b 1-2 7

New Sheet Layout (Customers are also grouped)
Cust P/N Jan Qty Feb Qty
a 1-1
a 1-2
b 1-1
b 1-2

 

[Max]

One play to try ..

Assume the monthly sheets
are named as: Jan, Feb, etc
with tables in cols A to C, data from row2 down, viz:

Cust P/N Qty
a 1-1 4
a 1-2 3
b 1-1 8
b 1-2 7

In your new sheet layout below,
let's revise the headers for cols C, D (in C1, D1, ... across)
from: "Jan Qty", "Feb Qty", etc
to just: Jan, Feb, etc (consistent with the actual sheetnames)

(Above will simplify it for us to use INDIRECT to read the col headers)

Cust P/N Jan Qty Feb Qty
a 1-1
a 1-2
b 1-1
b 1-2

Put in C2, and array-enter (press CTRL+SHIFT+ENTER):

=INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A
2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0))

Copy C2 across and fill down to populate the table

Adapt the ranges: A2:A100, C2:C100, etc to suit

And perhaps better with an error trap to return blanks: "" instead of errors
for a much cleaner looking output, we could put instead in C2, and
array-enter:

=IF(ISERROR(MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"
&C$1&"'!B2:B100"),0)),"",INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$
B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0)))

Then just copy C2 across and fill down as before to populate the table

 

[Guest]

Thanks for the help I'll try it out today.

One play to try ..

Assume the monthly sheets
are named as: Jan, Feb, etc
with tables in cols A to C, data from row2 down, viz:


In your new sheet layout below,
let's revise the headers for cols C, D (in C1, D1, ... across)
from: "Jan Qty", "Feb Qty", etc
to just: Jan, Feb, etc (consistent with the actual sheetnames)

(Above will simplify it for us to use INDIRECT to read the col headers)


Put in C2, and array-enter (press CTRL+SHIFT+ENTER):

=INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A
2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0))

Copy C2 across and fill down to populate the table

Adapt the ranges: A2:A100, C2:C100, etc to suit

And perhaps better with an error trap to return blanks: "" instead of errors
for a much cleaner looking output, we could put instead in C2, and
array-enter:

=IF(ISERROR(MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"
&C$1&"'!B2:B100"),0)),"",INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$
B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0)))

Then just copy C2 across and fill down as before to populate the table
--
Rgds
Max
xl 97

 

[Max]

You're welcome
Thanks for posting back ..