In a page €œHistory€� I have three columns of data (50,000+ rows) Name, Date
and Salary, and in a separate Page €œMonthly€� I have Name, Monthly Pay Date
and Monthly Salary.

Working in €œMonthly€� I need a formula to look in €œHistory€� to find the
person in Name and within their list of dates find the correct pay.

This is an example of €œHistory€�:
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a formula to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

Insert a new column C in the History sheet, and enter this formula in
C2:

=A2&B2

Copy this formula down by double-clicking the fill handle (the small
black square in the bottom right corner of the cursor, with C2
selected). You could put "Name_date" as the heading in C1.

Then in the Monthly sheet you would need this formula in C2:

=VLOOKUP(A2&B2,History!C$2:D$50000,2)/12

to give you the monthly salary, assuming Name in column A and Date in
column B and that the salary in the History sheet is annual salary.
This also assumes that the main table is sorted by name and date.

Hope this helps.

Pete

Charles wrote:
In a page "History" I have three columns of data (50,000+ rows) Name, Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly Pay Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a formula to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

=INDEX(History!C1:C100,MATCH(1,(History!A1:A1000=A 2)*(History!B1:B1000=B2),0
))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
In a page "History" I have three columns of data (50,000+ rows) Name, Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly Pay Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a formula
to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

Hi Pete

Thanks very much - a fantasic simple solution and I was trying INDEX and
MATCH.

Your solutions works perfectly.

Regards

Charles

"Pete_UK" wrote:

Insert a new column C in the History sheet, and enter this formula in
C2:

=A2&B2

Copy this formula down by double-clicking the fill handle (the small
black square in the bottom right corner of the cursor, with C2
selected). You could put "Name_date" as the heading in C1.

Then in the Monthly sheet you would need this formula in C2:

=VLOOKUP(A2&B2,History!C$2:D$50000,2)/12

to give you the monthly salary, assuming Name in column A and Date in
column B and that the salary in the History sheet is annual salary.
This also assumes that the main table is sorted by name and date.

Hope this helps.

Pete

Charles wrote:
In a page "History" I have three columns of data (50,000+ rows) Name, Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly Pay Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a formula to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

Like me <G!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
Hi Pete

Thanks very much - a fantasic simple solution and I was trying INDEX and
MATCH.

Your solutions works perfectly.

Regards

Charles

"Pete_UK" wrote:

Insert a new column C in the History sheet, and enter this formula in
C2:

=A2&B2

Copy this formula down by double-clicking the fill handle (the small
black square in the bottom right corner of the cursor, with C2
selected). You could put "Name_date" as the heading in C1.

Then in the Monthly sheet you would need this formula in C2:

=VLOOKUP(A2&B2,History!C$2:D$50000,2)/12

to give you the monthly salary, assuming Name in column A and Date in
column B and that the salary in the History sheet is annual salary.
This also assumes that the main table is sorted by name and date.

Hope this helps.

Pete

Charles wrote:
In a page "History" I have three columns of data (50,000+ rows) Name,
Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly Pay
Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a
formula to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

Hi Bob

Thanks for your help, this is sort of what I was attempting but also doesn't
work; I wonder if it is because DAte and Monthly Pay Date do not match?

Regards

Charles

"Bob Phillips" wrote:

=INDEX(History!C1:C100,MATCH(1,(History!A1:A1000=A 2)*(History!B1:B1000=B2),0
))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
In a page "History" I have three columns of data (50,000+ rows) Name, Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly Pay Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a formula
to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

Hi Charles,

thanks for the feedback, and I'm glad it worked for you - I like to
keep things simple !!

Pete

Charles wrote:
Hi Pete

Thanks very much - a fantasic simple solution and I was trying INDEX and
MATCH.

Your solutions works perfectly.

Regards

Charles

"Pete_UK" wrote:

Insert a new column C in the History sheet, and enter this formula in
C2:

=A2&B2

Copy this formula down by double-clicking the fill handle (the small
black square in the bottom right corner of the cursor, with C2
selected). You could put "Name_date" as the heading in C1.

Then in the Monthly sheet you would need this formula in C2:

=VLOOKUP(A2&B2,History!C$2:D$50000,2)/12

to give you the monthly salary, assuming Name in column A and Date in
column B and that the salary in the History sheet is annual salary.
This also assumes that the main table is sorted by name and date.

Hope this helps.

Pete

Charles wrote:
In a page "History" I have three columns of data (50,000+ rows) Name, Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly Pay Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a formula to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

At 2 o'clock in the morning, you have too <bg

Bob

"Pete_UK" wrote in message
ups.com...
Hi Charles,

thanks for the feedback, and I'm glad it worked for you - I like to
keep things simple !!

Pete

Charles wrote:
Hi Pete

Thanks very much - a fantasic simple solution and I was trying INDEX and
MATCH.

Your solutions works perfectly.

Regards

Charles

"Pete_UK" wrote:

Insert a new column C in the History sheet, and enter this formula in
C2:

=A2&B2

Copy this formula down by double-clicking the fill handle (the small
black square in the bottom right corner of the cursor, with C2
selected). You could put "Name_date" as the heading in C1.

Then in the Monthly sheet you would need this formula in C2:

=VLOOKUP(A2&B2,History!C$2:D$50000,2)/12

to give you the monthly salary, assuming Name in column A and Date in
column B and that the salary in the History sheet is annual salary.
This also assumes that the main table is sorted by name and date.

Hope this helps.

Pete

Charles wrote:
In a page "History" I have three columns of data (50,000+ rows)
Name, Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly
Pay Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find
the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a
formula to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

Do you mean that the date and paydate may only be the same month and year
for instance as against the same date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
Hi Bob

Thanks for your help, this is sort of what I was attempting but also
doesn't
work; I wonder if it is because DAte and Monthly Pay Date do not match?

Regards

Charles

"Bob Phillips" wrote:


=INDEX(History!C1:C100,MATCH(1,(History!A1:A1000=A 2)*(History!B1:B1000=B2),0
))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
In a page "History" I have three columns of data (50,000+ rows) Name,
Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly Pay
Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a
formula
to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

Bob,

reading between the lines, I think the History sheet represents the
salary history over several years for employees - every now and then
(eg 1st April) they get a pay rise putting them onto a different
salary. If you are doing the monthly pay run for January, for example,
you would want to match with the salary for the latest date for that
employee (not necessarily in the same year, even), which is why I used
VLOOKUP with an implied last parameter of TRUE - Charles wasn't looking
for an exact match on the current date.

Pete

Bob Phillips wrote:
Do you mean that the date and paydate may only be the same month and year
for instance as against the same date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
Hi Bob

Thanks for your help, this is sort of what I was attempting but also
doesn't
work; I wonder if it is because DAte and Monthly Pay Date do not match?

Regards

Charles

"Bob Phillips" wrote:


=INDEX(History!C1:C100,MATCH(1,(History!A1:A1000=A 2)*(History!B1:B1000=B2),0
))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
In a page "History" I have three columns of data (50,000+ rows) Name,
Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly Pay
Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find the
person in Name and within their list of dates find the correct pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a
formula
to
find Brown and then establish his pay rate which would be 1000 as at
01/Jan/00.

I hope this is clear!

Hi Pete,

I would have expected that, but in the original post, Charles said ... find
Brown and then establish his pay rate which would be 1000 as at 01/Jan/00
.... which I took literally because it was so definite.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pete_UK" wrote in message
ups.com...
Bob,

reading between the lines, I think the History sheet represents the
salary history over several years for employees - every now and then
(eg 1st April) they get a pay rise putting them onto a different
salary. If you are doing the monthly pay run for January, for example,
you would want to match with the salary for the latest date for that
employee (not necessarily in the same year, even), which is why I used
VLOOKUP with an implied last parameter of TRUE - Charles wasn't looking
for an exact match on the current date.

Pete

Bob Phillips wrote:
Do you mean that the date and paydate may only be the same month and
year
for instance as against the same date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
Hi Bob

Thanks for your help, this is sort of what I was attempting but also
doesn't
work; I wonder if it is because DAte and Monthly Pay Date do not
match?

Regards

Charles

"Bob Phillips" wrote:



=INDEX(History!C1:C100,MATCH(1,(History!A1:A1000=A 2)*(History!B1:B1000=B2),0
))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles" wrote in message
...
In a page "History" I have three columns of data (50,000+ rows)
Name,
Date
and Salary, and in a separate Page "Monthly" I have Name, Monthly
Pay
Date
and Monthly Salary.

Working in "Monthly" I need a formula to look in "History" to find
the
person in Name and within their list of dates find the correct
pay.

This is an example of "History":
Name Date Salary
Brown 01/Sep/99 750
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Brown 07/Sep/04 2200
Brown 01/Oct/05 2500
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/00 and the employee is Brown, I need a
formula
to
find Brown and then establish his pay rate which would be 1000 as
at
01/Jan/00.

I hope this is clear!