Help with Lookup Formula
Posted by Frank on January 10, 2002 3:23 AM
Can any kind soul help me with the formula to select the current pay rate for the
the named individual based on the worked day date. Basically I am appending to a list a name and date and I
require a lookup to go to a list of payscales and select the rate for the individual in force on the day worked.
Worked Days Pay Rates
Name Date Rate Name Effective Date Rate
Joe Bloggs 01/08/2001 6.00 Joe Bloggs 01/06/2001 6.00
Joe Bloggs 07/09/2001 6.00 Joe Bloggs 01/11/2001 6.50
Joe Bloggs 23/12/2001 6.50 Joe Bloggs 01/01/2002 7.00
Fred Smith 29/09/2001 5.00 Fred Smith 01/08/2001 5.00
Tom Brown 01/11/2001 5.50 Fred Smith 01/10/2001 5.50
Bert Green 01/11/2001 7.00 Tom Brown 01/10/2001 5.50
Tom Brown 20/02/2002 6.00 Tom Brown 01/02/2002 6.00
Bert Green 23/01/2002 7.50 Bert Green 01/07/2001 7.00
Bert Green 01/01/2002 7.50
Any help would be greatly appreciated
Posted by Chris D on January 10, 2002 11:24 AM
this looks a bit jumbled.... are there 3 sets of ranges here, one being your required lookup range ?
Posted by Sam S on January 10, 2002 8:40 PM
I think Chris has 3 columns e.g. Name, Date, Time of Shift Start. I tend to use a vlookup in the next couple of columns but there has to be an easier solution ie array formulas.
Posted by Frank on January 10, 2002 10:52 PM
Sorry about the jumble. Basically I have two sets of data, the first has three columns:- (A)=Name (B)=Date Worked (C)=Current Hourly Pay Rate.
I append columns A & B to a worksheet and then require a formula that will look up the hourly payrate from a seperate table of data.
The second table is a history of all pay rates and is (D) =Name (E)=Date hourly rate came into effect (F)= Hourly payrate. This second table of data
lists all names and the pay rate history for each individual along with the date the pay rate changed. The formula has to match column A to D then
using column B decide which pay rate (from column F)was in effect on the date the individual worked.
