lookup and display

[GAIDEN]

A C D G
H I J
Emp# Code Hours salary regular ot vacation
93 REGSAL 4.00 4.00 0.00 0.00 0.00
93 OVTIME 4.03 4.00 0.00 0.00 0.00
93 REGLAR 7.98 4.00 0.00 0.00 0.00
93 VACTON 8.00 4.00 0.00 0.00 0.00

I'm trying to display the hours worked (column D) in their respective
columns (G,H,I,J) but the only column that will display is column G. Here are
the formulas I'm using.

Salary: IF(VLOOKUP(A2,A:C,3,0)="REGSAL",VLOOKUP(A2,A,4,0),0)
Regular: IF(VLOOKUP(A2,A:C,3,0)="REGLAR",VLOOKUP(A2,A,4,0),0)
OT: IF(VLOOKUP(A2,A:C,3,0)="OVTIME",VLOOKUP(A2,A,4,0),0)
Vacation:IF(VLOOKUP(A2,A:C,3,0)="VACTON",VLOOKUP(A2,A,4,0),0)

 

[smartin]

A C D G
H I J
Emp# Code Hours salary regular ot vacation
93 REGSAL 4.00 4.00 0.00 0.00 0.00
93 OVTIME 4.03 4.00 0.00 0.00 0.00
93 REGLAR 7.98 4.00 0.00 0.00 0.00
93 VACTON 8.00 4.00 0.00 0.00 0.00

I'm trying to display the hours worked (column D) in their respective
columns (G,H,I,J) but the only column that will display is column G. Here are
the formulas I'm using.

Salary: IF(VLOOKUP(A2,A:C,3,0)="REGSAL",VLOOKUP(A2,A,4,0),0)
Regular: IF(VLOOKUP(A2,A:C,3,0)="REGLAR",VLOOKUP(A2,A,4,0),0)
OT: IF(VLOOKUP(A2,A:C,3,0)="OVTIME",VLOOKUP(A2,A,4,0),0)
Vacation:IF(VLOOKUP(A2,A:C,3,0)="VACTON",VLOOKUP(A2,A,4,0),0)

The problem is you are looking up the value in column A (emp#), not
column C (Code).

Forget the VLOOKUPs. I suggest the following:

Emp# Code Hours REGSAL OVTIME REGLAR VACTON
93 REGSAL 4 4 0 0 0
93 OVTIME 4.03 0 4.03 0 0
93 REGLAR 7.98 0 0 7.98 0
93 VACTON 8 0 0 0 8

The formula in $G$2 that can be pasted, and filled right and down
through $J$5 is
=SUMIF($C2,G$1,$D2)


Or you could do this if you like your column headers the way they are:

Emp# Code Hours salary regular ot vacation
93 REGSAL 4 4 0 0 0
93 OVTIME 4.03 0 4.03 0 0
93 REGLAR 7.98 0 0 7.98 0
93 VACTON 8 0 0 0 8

where the formulae in columns G:J are like
=IF($C2="REGSAL",$D2,0)
=IF($C2="OVTIME",$D2,0)
=IF($C2="REGLAR",$D2,0)
=IF($C2="VACTON",$D2,0)

 

[Max]

List these text as the headers instead in G1:J1 :
REGSAL, REGLAR, OVTIME, VACTON

Then you could place this in G2: =IF($C2=G$1,$D2,0)
Copy G2 across to J2, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik

 

[GAIDEN]

i was just advised to use
=SUMPRODUCT(--(A2:A10=A2),--(C2:C10="code"),D210)
and it worked. thanks though.

 

[GAIDEN]

i was just advised to use
=SUMPRODUCT(--(A2:A10=A2),--(C2:C10="code"),D210)
and it worked. thanks though

 

[Max]

No prob. Believe that wasn't your intents as expressed in your original post
here, though.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,300, Files: 356, Subscribers: 53
xdemechanik