still require help with lookup/match formula - please.

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?

Aladin

========

Aladin,
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
etc.
So the problem is to find the hourly payrate that was in effect for any given day worked by an employee.

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?

: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

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.

Aladin

=======

Re: Retrieval Question

Aladin,

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?

Frank

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:

{5;
6.5;
5;
6.5;
5.5;
5;
6;
7.5;
6.5;
7}

> 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.

Aladin

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

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

Re: Retrieval Question -SUCCESS!!!!

Aladin,

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!!!!!

regards
Frank