VLOOKUP

[DianeandChipps]

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

 

[joel]

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]

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]

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