Pierre,
You could use the HLOOKUP function.

1. With your rate table already created for center number and years.
a. Highlight the table Insert Name Define Type WorkCenterRate (or
other desired name) Add.
Giving you a table reference of: =Sheet1!$A$1:$M$73
2. Create a New Sheet(2).
A1=Leave Blank
A2 to M2= Use the same headers that you used on Sheet(1).
A3 to A74= Number them 1 through 72
B3=the following formula:

=if($A$1="","",HLOOKUP(YEAR($A$1),WorkCenterRate,( $A3+1))
The Pull down the formula in the B column

The "($A3+1)" references the row number to look at on sheet(1).


--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule
<<Previous Text Snipped to Save Bandwidth When Appropriate


"Pierre" wrote in message
...
Thank you Mike, I'll have a run at it.

Pierre


On Apr 25, 12:24 pm, Mike H wrote:
try this

=SUMPRODUCT((A2:A4=2)*(B1:D1=2008),(B2:D4))

this looks for w/c 2 in A2:A4 and 2008 in B1:D1 and returns the numeric
value at the intersect.

It would be better to make the 2 & the 2008 cell references.

Mike



"Pierre" wrote:
On a worksheet, we have 72 work centers, and charge rates for each
year and each center in a table; going out 12 years.

W/C 2008 2009 2010 etc
1 15.00 16.98 18.10
2 23.98 24.25 26.98
3 11.00 13.85 15.65
etc.

Would like to obtain the rate for each workcenter on another sheet for
the year thats entered into cell A1 in the worksheet.

A bunch of IF statements. . .=IF(YEAR(D14)=2008,TRUE,FALSE) could be
really clumsy as a place to start, as there are 12 years to chose
from, making the formulas particularly messy.

Thoughts?

Pierre- Hide quoted text -

- Show quoted text -