Lookup using more than one criteria

B

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
 
P

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:D$500,2,0)

and then copy this down.

Hope this helps.

Pete
 
B

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.
 
A

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top