software

I recently posted a question that Vito answered very well, however, my data
is more complicated than I originally explained.

Here is my revised problem:

I have one spreadsheet that contains MONTHLY pension payments with Employee
ID, the Monthly Pay Date and various Pensions details.

In the second spreadsheet I have a column of Employee ID (Note that the
Employee ID can differ only by an alpha character), a second with Pay Incease
Date and a third column with New Pay Rate; as show below:
EmployeeIncrease Date Pay Rate
76475324-May-01 9,531.60
76475324-May-02 10,038.60
764753 23-Jun-02 11,823.24
764753 24-Jun-02 10,038.60
76475301-Sep-02 10,565.88
76475322-Sep-02 12,431.64
76475324-May-03 12,634.44
764753M15-Sep-01 4,612.14
764753M 09-Jun-02 4,612.14
764753M15-Sep-02 5,105.62
764753M09-Dec-02 10,141.30
76476121-May-01 15,500.00
764761 01-Jun-02 17,000.00
76476101-Sep-02 17,385.00
76476101-Sep-03 17,907.00
76476101-Sep-04 19,000.00
76476101-Aug-05 24,000.00
764761B05-Dec-04 9,775.35
764761M01-May-02 27,000.00
764761M01-Sep-02 28,000.00

Say I have Employee 764761B with a paydate of 30-Jan-02, I need to find the
LAST pay increase (the CUREENT rate), in this case 9,775.35.

Thanks in advance your the always excellent help given.

Charles
=INDEX(C1:C21,MATCH(MAX(IF(A1:A21=quot;764761Bquot;,B1:B21 )),B1:B21,0))

as an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;macshimiquot; gt; wrote in message
...
gt; I recently posted a question that Vito answered very well, however, my
data
gt; is more complicated than I originally explained.
gt;
gt; Here is my revised problem:
gt;
gt; I have one spreadsheet that contains MONTHLY pension payments with
Employee
gt; ID, the Monthly Pay Date and various Pensions details.
gt;
gt; In the second spreadsheet I have a column of Employee ID (Note that the
gt; Employee ID can differ only by an alpha character), a second with Pay
Incease
gt; Date and a third column with New Pay Rate; as show below:
gt; Employee Increase Date Pay Rate
gt; 764753 24-May-01 9,531.60
gt; 764753 24-May-02 10,038.60
gt; 764753 23-Jun-02 11,823.24
gt; 764753 24-Jun-02 10,038.60
gt; 764753 01-Sep-02 10,565.88
gt; 764753 22-Sep-02 12,431.64
gt; 764753 24-May-03 12,634.44
gt; 764753M 15-Sep-01 4,612.14
gt; 764753M 09-Jun-02 4,612.14
gt; 764753M 15-Sep-02 5,105.62
gt; 764753M 09-Dec-02 10,141.30
gt; 764761 21-May-01 15,500.00
gt; 764761 01-Jun-02 17,000.00
gt; 764761 01-Sep-02 17,385.00
gt; 764761 01-Sep-03 17,907.00
gt; 764761 01-Sep-04 19,000.00
gt; 764761 01-Aug-05 24,000.00
gt; 764761B 05-Dec-04 9,775.35
gt; 764761M 01-May-02 27,000.00
gt; 764761M 01-Sep-02 28,000.00
gt;
gt; Say I have Employee 764761B with a paydate of 30-Jan-02, I need to find
the
gt; LAST pay increase (the CUREENT rate), in this case 9,775.35.
gt;
gt; Thanks in advance your the always excellent help given.
gt;
gt; Charles
gt;
gt;
Hi Bob

This works on the sample, I will try it on the entire spreadsheet, thousands
of entries.

Thanks very much, you guys on this newsgroup are life-savers!

Regards

Charles

quot;Bob Phillipsquot; wrote:

gt; =INDEX(C1:C21,MATCH(MAX(IF(A1:A21=quot;764761Bquot;,B1:B21 )),B1:B21,0))
gt;
gt; as an array formula, so commit with Ctrl-Shift-Enter.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;macshimiquot; gt; wrote in message
gt; ...
gt; gt; I recently posted a question that Vito answered very well, however, my
gt; data
gt; gt; is more complicated than I originally explained.
gt; gt;
gt; gt; Here is my revised problem:
gt; gt;
gt; gt; I have one spreadsheet that contains MONTHLY pension payments with
gt; Employee
gt; gt; ID, the Monthly Pay Date and various Pensions details.
gt; gt;
gt; gt; In the second spreadsheet I have a column of Employee ID (Note that the
gt; gt; Employee ID can differ only by an alpha character), a second with Pay
gt; Incease
gt; gt; Date and a third column with New Pay Rate; as show below:
gt; gt; Employee Increase Date Pay Rate
gt; gt; 764753 24-May-01 9,531.60
gt; gt; 764753 24-May-02 10,038.60
gt; gt; 764753 23-Jun-02 11,823.24
gt; gt; 764753 24-Jun-02 10,038.60
gt; gt; 764753 01-Sep-02 10,565.88
gt; gt; 764753 22-Sep-02 12,431.64
gt; gt; 764753 24-May-03 12,634.44
gt; gt; 764753M 15-Sep-01 4,612.14
gt; gt; 764753M 09-Jun-02 4,612.14
gt; gt; 764753M 15-Sep-02 5,105.62
gt; gt; 764753M 09-Dec-02 10,141.30
gt; gt; 764761 21-May-01 15,500.00
gt; gt; 764761 01-Jun-02 17,000.00
gt; gt; 764761 01-Sep-02 17,385.00
gt; gt; 764761 01-Sep-03 17,907.00
gt; gt; 764761 01-Sep-04 19,000.00
gt; gt; 764761 01-Aug-05 24,000.00
gt; gt; 764761B 05-Dec-04 9,775.35
gt; gt; 764761M 01-May-02 27,000.00
gt; gt; 764761M 01-Sep-02 28,000.00
gt; gt;
gt; gt; Say I have Employee 764761B with a paydate of 30-Jan-02, I need to find
gt; the
gt; gt; LAST pay increase (the CUREENT rate), in this case 9,775.35.
gt; gt;
gt; gt; Thanks in advance your the always excellent help given.
gt; gt;
gt; gt; Charles
gt; gt;
gt; gt;
gt;
gt;
gt;

Hi Bob

I have been trying this out and, unfortunately, it only works if there is a
single entry for an employee.

If you look at 764753M there are a number of different pay rates and I need
to find the Employee ID and the rate before the paydate, ie if the emplyee
had an increase in Jun 02 and another in Sep 05 then the pay date in Aug 05
must use the pay rate from Jun 05.

Looking forward to more of your expertise.

Regards

Charles

quot;Bob Phillipsquot; wrote:

gt; =INDEX(C1:C21,MATCH(MAX(IF(A1:A21=quot;764761Bquot;,B1:B21 )),B1:B21,0))
gt;
gt; as an array formula, so commit with Ctrl-Shift-Enter.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;macshimiquot; gt; wrote in message
gt; ...
gt; gt; I recently posted a question that Vito answered very well, however, my
gt; data
gt; gt; is more complicated than I originally explained.
gt; gt;
gt; gt; Here is my revised problem:
gt; gt;
gt; gt; I have one spreadsheet that contains MONTHLY pension payments with
gt; Employee
gt; gt; ID, the Monthly Pay Date and various Pensions details.
gt; gt;
gt; gt; In the second spreadsheet I have a column of Employee ID (Note that the
gt; gt; Employee ID can differ only by an alpha character), a second with Pay
gt; Incease
gt; gt; Date and a third column with New Pay Rate; as show below:
gt; gt; Employee Increase Date Pay Rate
gt; gt; 764753 24-May-01 9,531.60
gt; gt; 764753 24-May-02 10,038.60
gt; gt; 764753 23-Jun-02 11,823.24
gt; gt; 764753 24-Jun-02 10,038.60
gt; gt; 764753 01-Sep-02 10,565.88
gt; gt; 764753 22-Sep-02 12,431.64
gt; gt; 764753 24-May-03 12,634.44
gt; gt; 764753M 15-Sep-01 4,612.14
gt; gt; 764753M 09-Jun-02 4,612.14
gt; gt; 764753M 15-Sep-02 5,105.62
gt; gt; 764753M 09-Dec-02 10,141.30
gt; gt; 764761 21-May-01 15,500.00
gt; gt; 764761 01-Jun-02 17,000.00
gt; gt; 764761 01-Sep-02 17,385.00
gt; gt; 764761 01-Sep-03 17,907.00
gt; gt; 764761 01-Sep-04 19,000.00
gt; gt; 764761 01-Aug-05 24,000.00
gt; gt; 764761B 05-Dec-04 9,775.35
gt; gt; 764761M 01-May-02 27,000.00
gt; gt; 764761M 01-Sep-02 28,000.00
gt; gt;
gt; gt; Say I have Employee 764761B with a paydate of 30-Jan-02, I need to find
gt; the
gt; gt; LAST pay increase (the CUREENT rate), in this case 9,775.35.
gt; gt;
gt; gt; Thanks in advance your the always excellent help given.
gt; gt;
gt; gt; Charles
gt; gt;
gt; gt;
gt;
gt;
gt;

Put the date that you want to test against in E1 (Can simply be =TODAY() if
you want), and use

=INDEX($C$1:$C$21,MATCH(MAX(IF(($A$1:$A$21=quot;764753 Mquot;)*($B$1:$B$21lt;E1),$B$1:$
B$21)),$B$1:$B$21,0))

still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;macshimiquot; gt; wrote in message
...
gt; I recently posted a question that Vito answered very well, however, my
data
gt; is more complicated than I originally explained.
gt;
gt; Here is my revised problem:
gt;
gt; I have one spreadsheet that contains MONTHLY pension payments with
Employee
gt; ID, the Monthly Pay Date and various Pensions details.
gt;
gt; In the second spreadsheet I have a column of Employee ID (Note that the
gt; Employee ID can differ only by an alpha character), a second with Pay
Incease
gt; Date and a third column with New Pay Rate; as show below:
gt; Employee Increase Date Pay Rate
gt; 764753 24-May-01 9,531.60
gt; 764753 24-May-02 10,038.60
gt; 764753 23-Jun-02 11,823.24
gt; 764753 24-Jun-02 10,038.60
gt; 764753 01-Sep-02 10,565.88
gt; 764753 22-Sep-02 12,431.64
gt; 764753 24-May-03 12,634.44
gt; 764753M 15-Sep-01 4,612.14
gt; 764753M 09-Jun-02 4,612.14
gt; 764753M 15-Sep-02 5,105.62
gt; 764753M 09-Dec-02 10,141.30
gt; 764761 21-May-01 15,500.00
gt; 764761 01-Jun-02 17,000.00
gt; 764761 01-Sep-02 17,385.00
gt; 764761 01-Sep-03 17,907.00
gt; 764761 01-Sep-04 19,000.00
gt; 764761 01-Aug-05 24,000.00
gt; 764761B 05-Dec-04 9,775.35
gt; 764761M 01-May-02 27,000.00
gt; 764761M 01-Sep-02 28,000.00
gt;
gt; Say I have Employee 764761B with a paydate of 30-Jan-02, I need to find
the
gt; LAST pay increase (the CUREENT rate), in this case 9,775.35.
gt;
gt; Thanks in advance your the always excellent help given.
gt;
gt; Charles
gt;
gt;
Thanks Bob

This looks like it works now that I have explained myself properly!

quot;Bob Phillipsquot; wrote:

gt; Put the date that you want to test against in E1 (Can simply be =TODAY() if
gt; you want), and use
gt;
gt; =INDEX($C$1:$C$21,MATCH(MAX(IF(($A$1:$A$21=quot;764753 Mquot;)*($B$1:$B$21lt;E1),$B$1:$
gt; B$21)),$B$1:$B$21,0))
gt;
gt; still an array formula
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;macshimiquot; gt; wrote in message
gt; ...
gt; gt; I recently posted a question that Vito answered very well, however, my
gt; data
gt; gt; is more complicated than I originally explained.
gt; gt;
gt; gt; Here is my revised problem:
gt; gt;
gt; gt; I have one spreadsheet that contains MONTHLY pension payments with
gt; Employee
gt; gt; ID, the Monthly Pay Date and various Pensions details.
gt; gt;
gt; gt; In the second spreadsheet I have a column of Employee ID (Note that the
gt; gt; Employee ID can differ only by an alpha character), a second with Pay
gt; Incease
gt; gt; Date and a third column with New Pay Rate; as show below:
gt; gt; Employee Increase Date Pay Rate
gt; gt; 764753 24-May-01 9,531.60
gt; gt; 764753 24-May-02 10,038.60
gt; gt; 764753 23-Jun-02 11,823.24
gt; gt; 764753 24-Jun-02 10,038.60
gt; gt; 764753 01-Sep-02 10,565.88
gt; gt; 764753 22-Sep-02 12,431.64
gt; gt; 764753 24-May-03 12,634.44
gt; gt; 764753M 15-Sep-01 4,612.14
gt; gt; 764753M 09-Jun-02 4,612.14
gt; gt; 764753M 15-Sep-02 5,105.62
gt; gt; 764753M 09-Dec-02 10,141.30
gt; gt; 764761 21-May-01 15,500.00
gt; gt; 764761 01-Jun-02 17,000.00
gt; gt; 764761 01-Sep-02 17,385.00
gt; gt; 764761 01-Sep-03 17,907.00
gt; gt; 764761 01-Sep-04 19,000.00
gt; gt; 764761 01-Aug-05 24,000.00
gt; gt; 764761B 05-Dec-04 9,775.35
gt; gt; 764761M 01-May-02 27,000.00
gt; gt; 764761M 01-Sep-02 28,000.00
gt; gt;
gt; gt; Say I have Employee 764761B with a paydate of 30-Jan-02, I need to find
gt; the
gt; gt; LAST pay increase (the CUREENT rate), in this case 9,775.35.
gt; gt;
gt; gt; Thanks in advance your the always excellent help given.
gt; gt;
gt; gt; Charles
gt; gt;
gt; gt;
gt;
gt;
gt;