Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup using more than one criteria
Thanks for the feedback, Beccy.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Multiple Criteria return One answer | Excel Worksheet Functions | |||
Lookup with Multiple Criteria | Excel Discussion (Misc queries) | |||
Multiple criteria LOOKUP | Excel Worksheet Functions | |||
Lookup: 2 criteria | Excel Discussion (Misc queries) | |||
Conditional Lookup on Multiple Criteria | Excel Worksheet Functions |