lookup and display
 

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:D,4,0 ),0)
Regular: IF(VLOOKUP(A2,A:C,3,0)="REGLAR",VLOOKUP(A2,A:D,4,0 ),0)
OT: IF(VLOOKUP(A2,A:C,3,0)="OVTIME",VLOOKUP(A2,A:D,4,0 ),0)
Vacation:IF(VLOOKUP(A2,A:C,3,0)="VACTON",VLOOKUP(A 2,A:D,4,0),0)

lookup and display
 

GAIDEN wrote:
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:D,4,0 ),0)
Regular: IF(VLOOKUP(A2,A:C,3,0)="REGLAR",VLOOKUP(A2,A:D,4,0 ),0)
OT: IF(VLOOKUP(A2,A:C,3,0)="OVTIME",VLOOKUP(A2,A:D,4,0 ),0)
Vacation:IF(VLOOKUP(A2,A:C,3,0)="VACTON",VLOOKUP(A 2,A:D,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 a

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)

lookup and display
 

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" wrote:
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:D,4,0 ),0)
Regular: IF(VLOOKUP(A2,A:C,3,0)="REGLAR",VLOOKUP(A2,A:D,4,0 ),0)
OT: IF(VLOOKUP(A2,A:C,3,0)="OVTIME",VLOOKUP(A2,A:D,4,0 ),0)
Vacation:IF(VLOOKUP(A2,A:C,3,0)="VACTON",VLOOKUP(A 2,A:D,4,0),0)

lookup and display
 

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

"Max" wrote:

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" wrote:
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:D,4,0 ),0)
Regular: IF(VLOOKUP(A2,A:C,3,0)="REGLAR",VLOOKUP(A2,A:D,4,0 ),0)
OT: IF(VLOOKUP(A2,A:C,3,0)="OVTIME",VLOOKUP(A2,A:D,4,0 ),0)
Vacation:IF(VLOOKUP(A2,A:C,3,0)="VACTON",VLOOKUP(A 2,A:D,4,0),0)

lookup and display
 

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

"smartin" wrote:

GAIDEN wrote:
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:D,4,0 ),0)
Regular: IF(VLOOKUP(A2,A:C,3,0)="REGLAR",VLOOKUP(A2,A:D,4,0 ),0)
OT: IF(VLOOKUP(A2,A:C,3,0)="OVTIME",VLOOKUP(A2,A:D,4,0 ),0)
Vacation:IF(VLOOKUP(A2,A:C,3,0)="VACTON",VLOOKUP(A 2,A:D,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 a

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)

lookup and display
 

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
---
"GAIDEN" wrote in message
...
i was just advised to use
=SUMPRODUCT(--(A2:A10=A2),--(C2:C10="code"),D2:D10)
and it worked. thanks though.