Determing Value From Date

[IanEmery]

Hi

Is it possible to look up a value to apply in a formula based on a dat
entered in a spreadsheet.

For example I enter a date in a cell A2 of 06/05/2006 I want this to b
used as a lookup of factors that are to be applied for each financia
year. In the case above this would be 2006 and a factor of 1.5

I have factors for 5 years from 2006 -2011 stored in another range o
spreadsheet.

Obviously I can do this with a lookup by entering every date an
allocating the appropriate factor - but is there a cleaner way of doin
this.

Thanks for any input :

 

[starguy]

you should put sample data for better understanding of your requirement

 

[starguy]

you should put sample data for better understanding of your requirement

 

[Toppers]

Either

=LOOKUP(YEAR(A1),{2006,2007,2008,2009,2010,2011},{1.5,2,2.5,3,3.5,4})


OR

2006 1.50
2007 2.00
2008 2.50
2009 3.00
2010 3.50
2011 4.00


=VLOOKUP(YEAR(A1),$C:$D,2,FALSE)

I prefer the latter as you can change the data without having to change the
formula.

HTH

 

[IanEmery]

Thanks for that - almost there.

However I need the look up to identify financial years not calenda
years.

Any suggestions ?

Many thanks for all your hel

 

[IanEmery]

Thanks for that - almost there.

However I need the look up to identify financial years not calenda
years.

Any suggestions ?

Many thanks for all your hel

 

[IanEmery]

Thanks for that - almost there.

However I need the look up to identify financial years not calenda
years.

Any suggestions ?

Many thanks for all your hel

 

[bplumhoff]

Hello Ian,

take
=VLOOKUP(YEAR(A1)-(A1<DATE(YEAR(A1),4,1)),$C$1:$D$6,2,FALSE)

C16:
2006 1.5
2007 2
2008 2.5
2009 3
2010 3.5
2011 4

Fiscal year 2006 is 1-Apr-2006 until 31-Mar-2007 here, right?

HTH,
Bernd