MrExcel Publishing
Your One Stop for Excel Tips & Solutions

still require help with lookup/match formula - please.

Posted by Frank Johnson on January 14, 2002 6:53 AM

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 in column (C)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 firstly has to match name (column A to D) then
using column (B) decide which pay rate, from column (F),was in effect on the date the individual worked.

Posted by Aladin Akyurek on January 14, 2002 8:57 AM

Frank --

Are you saying that any A and B pair is exactly available in D and E along with a pay rate in F? What I'm trying to say is: Suppose I worked on 12-Jan-02. So may name is in A, the date I worked in B. Is that the case that my name is also in D and the foregoing very same date is also in E, and there is a pay rate value in F?



Posted by Frank Johnson on January 14, 2002 10:44 PM

sorry about the lack of clarity. (A) = Name (B) = Date Worked i.e. 01/11/01, 02/11/01, 03/11/01 etc.,
(D) = Name (E) = Date of pay rise i.e. 01/04/01, 01/09/01,01/02/02
So the problem is to find the hourly payrate that was in effect for any given day worked by an employee.

Posted by Aladin Akyurek on January 15, 2002 2:35 AM

May I rephrase that? You want to retrieve the most recent pay rate from E that holds for an employee who is the subject of the retrieval. Right?

Posted by Frank Johnson on January 15, 2002 4:57 AM

:Table 1 :Table 2
:(A) :(B) :(C) :(D) :(E) :(F)
:Selection of Dates Worked :Pay Scale and effective Dates
:Name :Date :Hourly Rate :Name :Effective Date :New Rate
Joe Bloggs :23/08/2001 :£6.00 :JoeBloggs :01/07/2001 :£6.00
Joe Bloggs :01/10/2001 :£6.50 :JoeBloggs :01/09/2001 :£6.50
Fred Smith :01/07/2001 :£5.00 :JoeBloggs :01/01/2002 :£7.00
Joe Bloggs :09/10/2001 :£6.50 :Fred Smith :01/06/2001 :£5.00
Fred Smith :23/08/2001 :£5.50 :Fred Smith :01/08/2001 :£5.50
Tom Brown :10/07/2001 :£5.00 :Tom Brown :01/07/2001 :£5.00
Tom Brown :23/08/2001 :£5.00 :Tom Brown :01/10/2001 :£7.50
Tom Brown :01/11/2001 :£7.50
Joe Bloggs :01/12/2001 :£6.50
Joe Bloggs :10/01/2002 :£7.00

:Formula required here in Column (C) to select hourly rate in force (from Column F) on date worked
:By selecting pay rate applicable for date worked in Column (B).

Alan does this make any more sense?

Hope so - Frank

Posted by Aladin Akyurek on January 15, 2002 8:43 AM

Retrieval Question

Frank --

No need to despair. ;)

Lets say that A1:C11 houses your "Table 1" data, where C shows expected results (fine that you included the):

{"Name ","Date ","Hourly Rate ";
"Joe Bloggs ",35664,6;
"Joe Bloggs ",35703,6.5;
"Fred Smith ",35611,5;
"Joe Bloggs ",35711,6.5;
"Fred Smith ",35664,5.5;
"Tom Brown ",35620,5;
"Tom Brown ",35664,5;
"Tom Brown ",35734,7.5;
"Joe Bloggs ",35764,6.5;
"Joe Bloggs ",35804,7}

Lets say that E1:G8 houses the historical "Table 2" data, which registers date-indexed pay rates for contracted employees:

{"Name ","Effective Date ","New Rate";
"Joe Bloggs ",35611,6;
"Joe Bloggs ",35673,6.5;
"Joe Bloggs ",35795,7;
"Fred Smith ",35581,5;
"Fred Smith ",35642,5.5;
"Tom Brown ",35611,5;
"Tom Brown ",35703,7.5}

You want a formula in C2 that retrieves from the second/historical set of data the pay rate which the latest, but not later than the corresponding day in B2. [ I knew you wanted this all along, but I had to verify. ]

In C2 enter: =INDEX(G:G,SUMPRODUCT(MAX((($E$2:$E$8=A2)*($F$2:$F$8 <= B2))*(ROW($G$2:$G$8)))))

Copy down this as far as needed.

Recommendations: Make each of the data columns in "Table" a named dynamic range and use these names in the above formula. If you do this, you can place "Table 2" in a worksheet of its own.



Posted by Frank Johnson on January 15, 2002 11:09 PM

Re: Retrieval Question


nearly there! - but if the name in column (A) is entered out of
sequence the formula doesn't appear to work. Is this because you
have formatted the date column in some way?


Posted by Aladin Akyurek on January 15, 2002 11:37 PM

Re: Retrieval Question

That would be most unwelcome.
When I exchange the names in A like this:

{"Name ","Date ";
"Tom Brown",35664;
"Joe Bloggs",35703;
"Fred Smith",35611;
"Joe Bloggs",35711;
"Fred Smith",35664;
"Tom Brown",35620;
"Joe Bloggs",35664;
"Tom Brown",35734;
"Joe Bloggs",35764;
"Joe Bloggs",35804}

The formula in C computes:


> Is this because you have formatted the date column in some way?

No. One thing that matters is that the names in A and in E must be spelled exactly the same. Beware of the leading and/or trailing spaces in names.


PS. If you want a copy of my workbook, drop me a line.

========= Is this because you

Posted by Frank Johnson on January 16, 2002 3:07 AM

Re: Retrieval Question -SUCCESS!!!!


you were right (as if there was any doubt) I had incorrectly
copied the names.
Many many thanks - the message board is right -
you are the master!!!!!