Lookup in Two Columns, Help needed with formula

[Charles]

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!

 

[Pete_UK]

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

 

[Bob Phillips]

=INDEX(History!C1:C100,MATCH(1,(History!A1:A1000=A2)*(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]

Hi Pete

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

Your solutions works perfectly.

Regards

Charles

 

[Bob Phillips]

Like me <G>!

--
HTH

Bob Phillips

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

 

[Charles]

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

 

[Pete_UK]

Hi Charles,

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

Pete

 

[Bob Phillips]

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

Bob

 

[Bob Phillips]

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)

 

[Pete_UK]

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]

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)