Sumproduct I think but a tough one

[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

 

[T. Valko]

Range d10:d129 contains costing data

Is that supposed to be:

Range D10:Q129 contains costing data

 

[Curtis]

Yes

Sorry and Thanks

Is that supposed to be:

Range D10:Q129 contains costing data

--
Biff
Microsoft Excel MVP





.

 

[barry houdini]

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

 

[Curtis]

Thx alot