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