Sumproduct I think but a tough one

  • Thread starter Thread starter Curtis
  • Start date Start date
C

Curtis

Source Sheet

D8:Q8 contains data -1 through 12 which represents months of the current
year (1 thru 12)and the 2 from the previous year (-1, and 0)

Range B10: b129 contains account numbers

Range d10:d129 contains costing data

Summary Sheet

Cell b3 will contain either -1 through 12 based on user request
Row J will contain the account # (j1 =100, J2=200, etc..)
The rest of the document requires no other intervention as it is preset with
values.


I need a formula that will lookup cell B3 and J6 in the summary sheet and
look to the source sheet and show me the costing

Hope that makes sense


Thanks alot
 
Range d10:d129 contains costing data

Is that supposed to be:

Range D10:Q129 contains costing data
 
Are B10:B129 all different? If so you could use

=INDEX('Source'!D$10:Q$129,MATCH(J1,'Source'!B$10:B$129,0),MATCH(B
$3,'Source'!D$8:Q$8,0))

regards, barry

or if there might be multiple matches in column B

=SUMIF('Source'!B$10:B$129,J1,INDEX('Source'!D$10:Q$129,0,MATCH(B
$3,'Source'!D$8:Q$8,0)))

regards, barry
 
Back
Top