Lookup using more than one criteria
 

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

Lookup using more than one criteria
 

Try this:

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

HTH
--
AP

"Beccy" a écrit dans le message de news:
...
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

Lookup using more than one criteria
 

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

Lookup using more than one criteria
 

Thanks Pete - this certainly works!

"Pete_UK" wrote:

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

Lookup using more than one criteria
 

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" wrote:

Try this:

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

HTH
--
AP

"Beccy" a écrit dans le message de news:
...
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

Lookup using more than one criteria
 

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

Cheers,
--
AP

"Beccy" a écrit dans le message de news:
...
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" wrote:

Try this:

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

HTH
--
AP

"Beccy" a écrit dans le message de
news:
...
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

Lookup using more than one criteria
 

Brilliant thanks

"Ardus Petus" wrote:

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

Cheers,
--
AP

"Beccy" a écrit dans le message de news:
...
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" wrote:

Try this:

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

HTH
--
AP

"Beccy" a écrit dans le message de
news:
...
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

Lookup using more than one criteria
 

Thanks for the feedback, Beccy.

Pete