Lookup Two Columns - Again

macshimi · 01-10-2006, 06:55 AM

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:
Employee Increase Date Pay Rate
764753 24-May-01 9,531.60
764753 24-May-02 10,038.60
764753 23-Jun-02 11,823.24
764753 24-Jun-02 10,038.60
764753 01-Sep-02 10,565.88
764753 22-Sep-02 12,431.64
764753 24-May-03 12,634.44
764753M 15-Sep-01 4,612.14
764753M 09-Jun-02 4,612.14
764753M 15-Sep-02 5,105.62
764753M 09-Dec-02 10,141.30
764761 21-May-01 15,500.00
764761 01-Jun-02 17,000.00
764761 01-Sep-02 17,385.00
764761 01-Sep-03 17,907.00
764761 01-Sep-04 19,000.00
764761 01-Aug-05 24,000.00
764761B 05-Dec-04 9,775.35
764761M 01-May-02 27,000.00
764761M 01-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

Bob Phillips · 01-10-2006, 07:45 AM

=INDEX(C1:C21,MATCH(MAX(IF(A1:A21="764761B",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)

"macshimi" <macshimi@discussions.microsoft.com> wrote in message
news:A7B41D6A-9466-4B38-AB02-3BD61798764E@microsoft.com...
> 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:
> Employee Increase Date Pay Rate
> 764753 24-May-01 9,531.60
> 764753 24-May-02 10,038.60
> 764753 23-Jun-02 11,823.24
> 764753 24-Jun-02 10,038.60
> 764753 01-Sep-02 10,565.88
> 764753 22-Sep-02 12,431.64
> 764753 24-May-03 12,634.44
> 764753M 15-Sep-01 4,612.14
> 764753M 09-Jun-02 4,612.14
> 764753M 15-Sep-02 5,105.62
> 764753M 09-Dec-02 10,141.30
> 764761 21-May-01 15,500.00
> 764761 01-Jun-02 17,000.00
> 764761 01-Sep-02 17,385.00
> 764761 01-Sep-03 17,907.00
> 764761 01-Sep-04 19,000.00
> 764761 01-Aug-05 24,000.00
> 764761B 05-Dec-04 9,775.35
> 764761M 01-May-02 27,000.00
> 764761M 01-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
>
>

macshimi · 01-10-2006, 08:15 AM

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

"Bob Phillips" wrote:

> =INDEX(C1:C21,MATCH(MAX(IF(A1:A21="764761B",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)
>
> "macshimi" <macshimi@discussions.microsoft.com> wrote in message
> news:A7B41D6A-9466-4B38-AB02-3BD61798764E@microsoft.com...
> > 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:
> > Employee Increase Date Pay Rate
> > 764753 24-May-01 9,531.60
> > 764753 24-May-02 10,038.60
> > 764753 23-Jun-02 11,823.24
> > 764753 24-Jun-02 10,038.60
> > 764753 01-Sep-02 10,565.88
> > 764753 22-Sep-02 12,431.64
> > 764753 24-May-03 12,634.44
> > 764753M 15-Sep-01 4,612.14
> > 764753M 09-Jun-02 4,612.14
> > 764753M 15-Sep-02 5,105.62
> > 764753M 09-Dec-02 10,141.30
> > 764761 21-May-01 15,500.00
> > 764761 01-Jun-02 17,000.00
> > 764761 01-Sep-02 17,385.00
> > 764761 01-Sep-03 17,907.00
> > 764761 01-Sep-04 19,000.00
> > 764761 01-Aug-05 24,000.00
> > 764761B 05-Dec-04 9,775.35
> > 764761M 01-May-02 27,000.00
> > 764761M 01-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
> >
> >
>
>
>

macshimi · 01-10-2006, 10:10 AM

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

"Bob Phillips" wrote:

> =INDEX(C1:C21,MATCH(MAX(IF(A1:A21="764761B",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)
>
> "macshimi" <macshimi@discussions.microsoft.com> wrote in message
> news:A7B41D6A-9466-4B38-AB02-3BD61798764E@microsoft.com...
> > 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:
> > Employee Increase Date Pay Rate
> > 764753 24-May-01 9,531.60
> > 764753 24-May-02 10,038.60
> > 764753 23-Jun-02 11,823.24
> > 764753 24-Jun-02 10,038.60
> > 764753 01-Sep-02 10,565.88
> > 764753 22-Sep-02 12,431.64
> > 764753 24-May-03 12,634.44
> > 764753M 15-Sep-01 4,612.14
> > 764753M 09-Jun-02 4,612.14
> > 764753M 15-Sep-02 5,105.62
> > 764753M 09-Dec-02 10,141.30
> > 764761 21-May-01 15,500.00
> > 764761 01-Jun-02 17,000.00
> > 764761 01-Sep-02 17,385.00
> > 764761 01-Sep-03 17,907.00
> > 764761 01-Sep-04 19,000.00
> > 764761 01-Aug-05 24,000.00
> > 764761B 05-Dec-04 9,775.35
> > 764761M 01-May-02 27,000.00
> > 764761M 01-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
> >
> >
>
>
>

Bob Phillips · 01-10-2006, 10:20 AM

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="764753M")*($B$1:$B$21<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)

"macshimi" <macshimi@discussions.microsoft.com> wrote in message
news:A7B41D6A-9466-4B38-AB02-3BD61798764E@microsoft.com...
> 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:
> Employee Increase Date Pay Rate
> 764753 24-May-01 9,531.60
> 764753 24-May-02 10,038.60
> 764753 23-Jun-02 11,823.24
> 764753 24-Jun-02 10,038.60
> 764753 01-Sep-02 10,565.88
> 764753 22-Sep-02 12,431.64
> 764753 24-May-03 12,634.44
> 764753M 15-Sep-01 4,612.14
> 764753M 09-Jun-02 4,612.14
> 764753M 15-Sep-02 5,105.62
> 764753M 09-Dec-02 10,141.30
> 764761 21-May-01 15,500.00
> 764761 01-Jun-02 17,000.00
> 764761 01-Sep-02 17,385.00
> 764761 01-Sep-03 17,907.00
> 764761 01-Sep-04 19,000.00
> 764761 01-Aug-05 24,000.00
> 764761B 05-Dec-04 9,775.35
> 764761M 01-May-02 27,000.00
> 764761M 01-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
>
>

macshimi · 01-10-2006, 10:45 AM

Thanks Bob

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

"Bob Phillips" wrote:

> 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="764753M")*($B$1:$B$21<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)
>
> "macshimi" <macshimi@discussions.microsoft.com> wrote in message
> news:A7B41D6A-9466-4B38-AB02-3BD61798764E@microsoft.com...
> > 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:
> > Employee Increase Date Pay Rate
> > 764753 24-May-01 9,531.60
> > 764753 24-May-02 10,038.60
> > 764753 23-Jun-02 11,823.24
> > 764753 24-Jun-02 10,038.60
> > 764753 01-Sep-02 10,565.88
> > 764753 22-Sep-02 12,431.64
> > 764753 24-May-03 12,634.44
> > 764753M 15-Sep-01 4,612.14
> > 764753M 09-Jun-02 4,612.14
> > 764753M 15-Sep-02 5,105.62
> > 764753M 09-Dec-02 10,141.30
> > 764761 21-May-01 15,500.00
> > 764761 01-Jun-02 17,000.00
> > 764761 01-Sep-02 17,385.00
> > 764761 01-Sep-03 17,907.00
> > 764761 01-Sep-04 19,000.00
> > 764761 01-Aug-05 24,000.00
> > 764761B 05-Dec-04 9,775.35
> > 764761M 01-May-02 27,000.00
> > 764761M 01-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
> >
> >
>
>
>