|
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
=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! |
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
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! |
Lookup in Two Columns, Help needed with formula
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! |
| All times are GMT +1. The time now is 09:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com