Lookup using more than one criteria

[Beccy]

Hi,

I have a large table of data relating to staff and their salary for the
month (about 500 rows). Each member of staff is coded against a cost centre,
and some members of staff have two jobs and therefore work against two
different cost centres.
I want to be able to do a vlookup to pull the pay for the month accross to a
monitoring sheet. But i need to be able to vlookup on cost centre and
employee?
If i just do a vlookup on employee then it will obviously sometimes bring
accross the pay from the wrong cost centre.

Table is eg.:

Cost Centre Employee ref Basic Pay April
L3840 J Bloggs £1,000
L3840 J Smith £500
L0060 P Jones £800
L0060 J Bloggs £300

So i need to say lookup J Bloggs in cost centre L3840 and bring me their pay
for April??

Help??

Thanks everyone!
Beccy

 

[Ardus Petus]

Try this:

=SUMPRODUCT((A1:A999="L3840")*(B1:B999="J Bloggs"),C1:C999)

HTH

 

[Pete_UK]

If your table occupies columns A to C, then highlight column C and
Insert | Column so that you have a new column C. You can add the
heading CC_Empref in C1 if you like, and in C2 add this formula:

=A2&B2

and copy this down your 500 rows. Assume this sheet is called Salaries.

In your monitoring sheet, assuming you also have a Cost Centre and an
Employee ref column (assume A and B again), then you can build up your
VLOOKUP( ) formula as follows:

=VLOOKUP(A2&B2,Salaries!C$2$500,2,0)

and then copy this down.

Hope this helps.

Pete

 

[Beccy]

Thanks Pete - this certainly works!

 

[Beccy]

Thanks Ardus. This works but is there a way that instead of having to type
each formula "J Bloggs" (as there are loads of employees!) that i can link it
to the cell in the monitoring sheet that says J Bloggs? Then i could just
copy the formula all the way down the page instead of typing each one.

 

[Ardus Petus]

Say you have "J Bloggs" in D1 and "L3840" in E1:
=SUMPRODUCT((A1:A999=$E$1)*(B1:B999=$D$1),C1:C999)

Cheers,

 

[Beccy]

Brilliant thanks

Say you have "J Bloggs" in D1 and "L3840" in E1:
=SUMPRODUCT((A1:A999=$E$1)*(B1:B999=$D$1),C1:C999)

Cheers,

 

[Pete_UK]

Thanks for the feedback, Beccy.

Pete