Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
I have been using a VLOOKUP formula to bring data from one worksheet to
another in the same workbook which has been working fine - up to a point. =VLOOKUP(G2,'Oct - Dec Database'!$G$2:$Q$18,3,FALSE) G2 is a payroll number which is unique and the third column of the array is the number of hours they worked in a month. Unfortunately there are three months of information, in column F, for most staff members on the worksheet and I was wondering if there is any way for the lookup to search for the payroll number and the month before giving an answer. The way I am doing it at the moment the results give the first answer for all months. For example: Joe Bloggs works 100 in October; 125 in November; 75 in December - the answer I get is 100 for all of his months. I hope that this makes enough sense and someone can help me. Many thanks Diane |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
You can use SumProduct with F2 = month number
=SumProduct(--(G2='Oct - Dec Database'!$G$2:$G$18),--(F2='Oct - Dec Database'!$F$2:$F$18),'Oct - Dec Database'!$I$2:$I$18) "DianeandChipps" wrote: I have been using a VLOOKUP formula to bring data from one worksheet to another in the same workbook which has been working fine - up to a point. =VLOOKUP(G2,'Oct - Dec Database'!$G$2:$Q$18,3,FALSE) G2 is a payroll number which is unique and the third column of the array is the number of hours they worked in a month. Unfortunately there are three months of information, in column F, for most staff members on the worksheet and I was wondering if there is any way for the lookup to search for the payroll number and the month before giving an answer. The way I am doing it at the moment the results give the first answer for all months. For example: Joe Bloggs works 100 in October; 125 in November; 75 in December - the answer I get is 100 for all of his months. I hope that this makes enough sense and someone can help me. Many thanks Diane |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
Thanks Joel your formula works very well.
I hadn't noticed however that some people had worked on more than one project in the same month which means your formula has added all of the monthly totals. The monthly hours is the number of paid hours in the month but not the hours worked on a project, I should have made that clear. I have never used the SUMPRODUCT formula before can you explain exactly what the formual is doing and the significance of the --? Many thanks again, Diane "joel" wrote: You can use SumProduct with F2 = month number =SumProduct(--(G2='Oct - Dec Database'!$G$2:$G$18),--(F2='Oct - Dec Database'!$F$2:$F$18),'Oct - Dec Database'!$I$2:$I$18) "DianeandChipps" wrote: I have been using a VLOOKUP formula to bring data from one worksheet to another in the same workbook which has been working fine - up to a point. =VLOOKUP(G2,'Oct - Dec Database'!$G$2:$Q$18,3,FALSE) G2 is a payroll number which is unique and the third column of the array is the number of hours they worked in a month. Unfortunately there are three months of information, in column F, for most staff members on the worksheet and I was wondering if there is any way for the lookup to search for the payroll number and the month before giving an answer. The way I am doing it at the moment the results give the first answer for all months. For example: Joe Bloggs works 100 in October; 125 in November; 75 in December - the answer I get is 100 for all of his months. I hope that this makes enough sense and someone can help me. Many thanks Diane |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
Sumproduct works on arrays. A range of cells will be converted to an array
as shown by the numberis in the bracket below For example Y = Sumproduct({1,2,3,4},{5,6,7,8},{9,10,11,12}) which is reallly Y = (1 * 5 * 9) + (2 * 6 * 9) + (3 * 7 * 11) + (4 * 8 * 12) In my formula --(G2='Oct - Dec Database'!$G$2:$G$18) Is producing an array of True or False --({G2='Oct - Dec Database'!G2,G2='Oct - Dec Database'!G3,......}) Which will result in --(True,False,False,True,.....) Now have you been wondering what the two dashes are in front of the formula Well it changes True to 1 and False to 0. This is my formula below =SumProduct(--(G2='Oct - Dec Database'!$G$2:$G$18),--(F2='Oct - Dec Database'!$F$2:$F$18),'Oct - Dec Database'!$I$2:$I$18) There is thre parts to the formula 1) Comparing G2 to each value in column G which give yo a 1 for each matched item. Multiply by 1 will get you your value. For False, multiplying by zero will move the data from the results 2) Comparing F2 to each value in column F 3) The 3rd part of the formula are the values you want to look at. Only the values that have 1's in the first two parts will be returned because multiply by the 0's will remove the items you don't want. So here is an easy example Y = ({1,1,0},{1,0,1},{3,7,11}) Y = (1 * 1 * 3) + (1 * 0 * 7) + (0 * 1 * 11) Y = 3 "DianeandChipps" wrote: Thanks Joel your formula works very well. I hadn't noticed however that some people had worked on more than one project in the same month which means your formula has added all of the monthly totals. The monthly hours is the number of paid hours in the month but not the hours worked on a project, I should have made that clear. I have never used the SUMPRODUCT formula before can you explain exactly what the formual is doing and the significance of the --? Many thanks again, Diane "joel" wrote: You can use SumProduct with F2 = month number =SumProduct(--(G2='Oct - Dec Database'!$G$2:$G$18),--(F2='Oct - Dec Database'!$F$2:$F$18),'Oct - Dec Database'!$I$2:$I$18) "DianeandChipps" wrote: I have been using a VLOOKUP formula to bring data from one worksheet to another in the same workbook which has been working fine - up to a point. =VLOOKUP(G2,'Oct - Dec Database'!$G$2:$Q$18,3,FALSE) G2 is a payroll number which is unique and the third column of the array is the number of hours they worked in a month. Unfortunately there are three months of information, in column F, for most staff members on the worksheet and I was wondering if there is any way for the lookup to search for the payroll number and the month before giving an answer. The way I am doing it at the moment the results give the first answer for all months. For example: Joe Bloggs works 100 in October; 125 in November; 75 in December - the answer I get is 100 for all of his months. I hope that this makes enough sense and someone can help me. Many thanks Diane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup problem - unable to get the vlookup property | Excel Programming | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |