software

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

Try this:

=SUMPRODUCT((A1:A999=quot;L3840quot;)*(B1:B999=quot;J Bloggsquot;),C1:C999)

HTH
--
AP

quot;Beccyquot; gt; a 閏rit dans le message de news:
...
gt; Hi,
gt;
gt; I have a large table of data relating to staff and their salary for the
gt; month (about 500 rows). Each member of staff is coded against a cost
gt; centre,
gt; and some members of staff have two jobs and therefore work against two
gt; different cost centres.
gt; I want to be able to do a vlookup to pull the pay for the month accross to
gt; a
gt; monitoring sheet. But i need to be able to vlookup on cost centre and
gt; employee?
gt; If i just do a vlookup on employee then it will obviously sometimes bring
gt; accross the pay from the wrong cost centre.
gt;
gt; Table is eg.:
gt;
gt; Cost Centre Employee ref Basic Pay April
gt; L3840 J Bloggs ?1,000
gt; L3840 J Smith ?500
gt; L0060 P Jones ?800
gt; L0060 J Bloggs ?300
gt;
gt; So i need to say lookup J Bloggs in cost centre L3840 and bring me their
gt; pay
gt; for April??
gt;
gt; Help??
gt;
gt; Thanks everyone!
gt; Beccy
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:

=A2amp;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(A2amp;B2,Salaries!C$2$500,2,0)

and then copy this down.

Hope this helps.

PeteThanks Pete - this certainly works!

quot;Pete_UKquot; wrote:

gt; If your table occupies columns A to C, then highlight column C and
gt; Insert | Column so that you have a new column C. You can add the
gt; heading CC_Empref in C1 if you like, and in C2 add this formula:
gt;
gt; =A2amp;B2
gt;
gt; and copy this down your 500 rows. Assume this sheet is called Salaries.
gt;
gt; In your monitoring sheet, assuming you also have a Cost Centre and an
gt; Employee ref column (assume A and B again), then you can build up your
gt; VLOOKUP( ) formula as follows:
gt;
gt; =VLOOKUP(A2amp;B2,Salaries!C$2$500,2,0)
gt;
gt; and then copy this down.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;

Thanks Ardus. This works but is there a way that instead of having to type
each formula quot;J Bloggsquot; (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.

quot;Ardus Petusquot; wrote:

gt; Try this:
gt;
gt; =SUMPRODUCT((A1:A999=quot;L3840quot;)*(B1:B999=quot;J Bloggsquot;),C1:C999)
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Beccyquot; gt; a 茅crit dans le message de news:
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; I have a large table of data relating to staff and their salary for the
gt; gt; month (about 500 rows). Each member of staff is coded against a cost
gt; gt; centre,
gt; gt; and some members of staff have two jobs and therefore work against two
gt; gt; different cost centres.
gt; gt; I want to be able to do a vlookup to pull the pay for the month accross to
gt; gt; a
gt; gt; monitoring sheet. But i need to be able to vlookup on cost centre and
gt; gt; employee?
gt; gt; If i just do a vlookup on employee then it will obviously sometimes bring
gt; gt; accross the pay from the wrong cost centre.
gt; gt;
gt; gt; Table is eg.:
gt; gt;
gt; gt; Cost Centre Employee ref Basic Pay April
gt; gt; L3840 J Bloggs 拢1,000
gt; gt; L3840 J Smith 拢500
gt; gt; L0060 P Jones 拢800
gt; gt; L0060 J Bloggs 拢300
gt; gt;
gt; gt; So i need to say lookup J Bloggs in cost centre L3840 and bring me their
gt; gt; pay
gt; gt; for April??
gt; gt;
gt; gt; Help??
gt; gt;
gt; gt; Thanks everyone!
gt; gt; Beccy
gt;
gt;
gt;

Say you have quot;J Bloggsquot; in D1 and quot;L3840quot; in E1:
=SUMPRODUCT((A1:A999=$E$1)*(B1:B999=$D$1),C1:C999)

Cheers,
--
AP

quot;Beccyquot; gt; a 閏rit dans le message de news:
...
gt; Thanks Ardus. This works but is there a way that instead of having to type
gt; each formula quot;J Bloggsquot; (as there are loads of employees!) that i can link
gt; it
gt; to the cell in the monitoring sheet that says J Bloggs? Then i could just
gt; copy the formula all the way down the page instead of typing each one.
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt;gt; Try this:
gt;gt;
gt;gt; =SUMPRODUCT((A1:A999=quot;L3840quot;)*(B1:B999=quot;J Bloggsquot;),C1:C999)
gt;gt;
gt;gt; HTH
gt;gt; --
gt;gt; AP
gt;gt;
gt;gt; quot;Beccyquot; gt; a 閏rit dans le message de
gt;gt; news:
gt;gt; ...
gt;gt; gt; Hi,
gt;gt; gt;
gt;gt; gt; I have a large table of data relating to staff and their salary for the
gt;gt; gt; month (about 500 rows). Each member of staff is coded against a cost
gt;gt; gt; centre,
gt;gt; gt; and some members of staff have two jobs and therefore work against two
gt;gt; gt; different cost centres.
gt;gt; gt; I want to be able to do a vlookup to pull the pay for the month accross
gt;gt; gt; to
gt;gt; gt; a
gt;gt; gt; monitoring sheet. But i need to be able to vlookup on cost centre and
gt;gt; gt; employee?
gt;gt; gt; If i just do a vlookup on employee then it will obviously sometimes
gt;gt; gt; bring
gt;gt; gt; accross the pay from the wrong cost centre.
gt;gt; gt;
gt;gt; gt; Table is eg.:
gt;gt; gt;
gt;gt; gt; Cost Centre Employee ref Basic Pay April
gt;gt; gt; L3840 J Bloggs ?1,000
gt;gt; gt; L3840 J Smith ?500
gt;gt; gt; L0060 P Jones ?800
gt;gt; gt; L0060 J Bloggs ?300
gt;gt; gt;
gt;gt; gt; So i need to say lookup J Bloggs in cost centre L3840 and bring me
gt;gt; gt; their
gt;gt; gt; pay
gt;gt; gt; for April??
gt;gt; gt;
gt;gt; gt; Help??
gt;gt; gt;
gt;gt; gt; Thanks everyone!
gt;gt; gt; Beccy
gt;gt;
gt;gt;
gt;gt;
Brilliant thanks

quot;Ardus Petusquot; wrote:

gt; Say you have quot;J Bloggsquot; in D1 and quot;L3840quot; in E1:
gt; =SUMPRODUCT((A1:A999=$E$1)*(B1:B999=$D$1),C1:C999)
gt;
gt; Cheers,
gt; --
gt; AP
gt;
gt; quot;Beccyquot; gt; a 茅crit dans le message de news:
gt; ...
gt; gt; Thanks Ardus. This works but is there a way that instead of having to type
gt; gt; each formula quot;J Bloggsquot; (as there are loads of employees!) that i can link
gt; gt; it
gt; gt; to the cell in the monitoring sheet that says J Bloggs? Then i could just
gt; gt; copy the formula all the way down the page instead of typing each one.
gt; gt;
gt; gt; quot;Ardus Petusquot; wrote:
gt; gt;
gt; gt;gt; Try this:
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT((A1:A999=quot;L3840quot;)*(B1:B999=quot;J Bloggsquot;),C1:C999)
gt; gt;gt;
gt; gt;gt; HTH
gt; gt;gt; --
gt; gt;gt; AP
gt; gt;gt;
gt; gt;gt; quot;Beccyquot; gt; a 茅crit dans le message de
gt; gt;gt; news:
gt; gt;gt; ...
gt; gt;gt; gt; Hi,
gt; gt;gt; gt;
gt; gt;gt; gt; I have a large table of data relating to staff and their salary for the
gt; gt;gt; gt; month (about 500 rows). Each member of staff is coded against a cost
gt; gt;gt; gt; centre,
gt; gt;gt; gt; and some members of staff have two jobs and therefore work against two
gt; gt;gt; gt; different cost centres.
gt; gt;gt; gt; I want to be able to do a vlookup to pull the pay for the month accross
gt; gt;gt; gt; to
gt; gt;gt; gt; a
gt; gt;gt; gt; monitoring sheet. But i need to be able to vlookup on cost centre and
gt; gt;gt; gt; employee?
gt; gt;gt; gt; If i just do a vlookup on employee then it will obviously sometimes
gt; gt;gt; gt; bring
gt; gt;gt; gt; accross the pay from the wrong cost centre.
gt; gt;gt; gt;
gt; gt;gt; gt; Table is eg.:
gt; gt;gt; gt;
gt; gt;gt; gt; Cost Centre Employee ref Basic Pay April
gt; gt;gt; gt; L3840 J Bloggs 拢1,000
gt; gt;gt; gt; L3840 J Smith 拢500
gt; gt;gt; gt; L0060 P Jones 拢800
gt; gt;gt; gt; L0060 J Bloggs 拢300
gt; gt;gt; gt;
gt; gt;gt; gt; So i need to say lookup J Bloggs in cost centre L3840 and bring me
gt; gt;gt; gt; their
gt; gt;gt; gt; pay
gt; gt;gt; gt; for April??
gt; gt;gt; gt;
gt; gt;gt; gt; Help??
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks everyone!
gt; gt;gt; gt; Beccy
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

Thanks for the feedback, Beccy.

Pete