Determing Value From Date

[IanEmery]

Hi

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

For example I enter a date in a cell A2 of 06/05/2006 I want this to be used as a lookup of factors that are to be applied for each financial 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 of spreadsheet.

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

Thanks for any input

[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" wrote:

>
> Hi
>
> Is it possible to look up a value to apply in a formula based on a date
> entered in a spreadsheet.
>
> For example I enter a date in a cell A2 of 06/05/2006 I want this to be
> used as a lookup of factors that are to be applied for each financial
> 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 of
> spreadsheet.
>
> Obviously I can do this with a lookup by entering every date and
> allocating the appropriate factor - but is there a cleaner way of doing
> this.
>
> Thanks for any input
>
>
> --
> IanEmery
> ------------------------------------------------------------------------
> IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714
> View this thread: http://www.excelforum.com/showthread...hreadid=562374
>
>

[IanEmery]

Thanks for that - almost there.

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

Any suggestions ?

Many thanks for all your help

[bplumhoff@gmail.com]

Hello Ian,

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

C1:D6:
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