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

you should put sample data for better understanding of your requirement.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=562374

you should put sample data for better understanding of your requirement.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=562374

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

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


--
IanEmery
------------------------------------------------------------------------
IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714
View this thread: http://www.excelforum.com/showthread...hreadid=562374

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


--
IanEmery
------------------------------------------------------------------------
IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714
View this thread: http://www.excelforum.com/showthread...hreadid=562374

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


--
IanEmery
------------------------------------------------------------------------
IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714
View this thread: http://www.excelforum.com/showthread...hreadid=562374

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