look up tables

[rev]

Hi Folks,

Please can someone let me know the easiest way to pick a rate based on a the type of work being done and the date the work was done. Both of these variables will be input by the user.

Eg. if the work is type 1 and the date is between 2001-2002 rate = $5 if date is between 2002-2003 then $10, 2004 - 2005 rate = $15.

whereas if the work is type 2 and the date is between 2001-2002 rate = $20 if date is between 2002-2003 then $40, 2004 - 2005 rate = $60.

I was going to use the lookup function but im not sure how to make it work when the date is within a certain range eg before dd/mm/yy or after dd/m/yy.

Thanks in advance for any help!

Rev.

[starguy]

Hi Folks,

Please can someone let me know the easiest way to pick a rate based on a the type of work being done and the date the work was done. Both of these variables will be input by the user.

Eg. if the work is type 1 and the date is between 2001-2002 rate = $5 if date is between 2002-2003 then $10, 2004 - 2005 rate = $15.

whereas if the work is type 2 and the date is between 2001-2002 rate = $20 if date is between 2002-2003 then $40, 2004 - 2005 rate = $60.

I was going to use the lookup function but im not sure how to make it work when the date is within a certain range eg before dd/mm/yy or after dd/m/yy.

Thanks in advance for any help!

Rev.

Please attach a sample file in zip format.

[rev]

Hi Starguy

example uploaded to >> http://putstuff.putfile.com/73417/1120071

http://f13.putfile.com/getfile/10505...23/example.xls

The user would input the date of work and number of hours from which i would like excel to calculate the rate to be used based on the type of work listed in column A and the year of work from column B - E. eg if user inputs Type of Work = AOT and Date of work = 04/04/2006 then excel would look up the rate for AOT in 2006


thanks!

[starguy]

Hi Starguy

example uploaded to >> http://putstuff.putfile.com/73417/1120071

http://f13.putfile.com/getfile/10505...23/example.xls

The user would input the date of work and number of hours from which i would like excel to calculate the rate to be used based on the type of work listed in column A and the year of work from column B - E. eg if user inputs Type of Work = AOT and Date of work = 04/04/2006 then excel would look up the rate for AOT in 2006


thanks!

see the attached file.
Formula used is array formula and must be entered by pressing Ctrl+Shift+Enter

[rev]

Thanks Starguy!

Thats doing what I wanted it to do although I think I may have chosen poor dates to use in my example.

Am I correct in thinking that excel is simply matching the year that is entered in B13?

I ask this as, on occasion, there may be more than 1 change in rate within each year eg 15th of feb 06 = 15 but changing to 17.5 on the 20th of July 06 and possibly even a further change in december. Would this formula be able to select the correct rate in this case?

I was hoping to find a formula that would run along the lines of where B13 is greater than "31/12/03" but less than "01/01/05" it would select column B and then match the type of work as in the formula u have kindly provided.

I hope that this makes sense! (and is possible) ;-) I would appreciate any suggestions on the best way to do this.

I apologise if I have made this more complicated, by my poor choice of dates in the example, but thank you for taking the time to look at this for me!

Rev.

[starguy]

Thanks Starguy!

Thats doing what I wanted it to do although I think I may have chosen poor dates to use in my example.

Am I correct in thinking that excel is simply matching the year that is entered in B13?

I ask this as, on occasion, there may be more than 1 change in rate within each year eg 15th of feb 06 = 15 but changing to 17.5 on the 20th of July 06 and possibly even a further change in december. Would this formula be able to select the correct rate in this case?

I was hoping to find a formula that would run along the lines of where B13 is greater than "31/12/03" but less than "01/01/05" it would select column B and then match the type of work as in the formula u have kindly provided.

I hope that this makes sense! (and is possible) ;-) I would appreciate any suggestions on the best way to do this.

I apologise if I have made this more complicated, by my poor choice of dates in the example, but thank you for taking the time to look at this for me!

Rev.

Please provide a sample of your requirement in zip format.