lookup and display

  • Thread starter Thread starter GAIDEN
  • Start date Start date
G

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: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(A2,A:D,4,0),0)
 
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
 
i was just advised to use
=SUMPRODUCT(--(A2:A10=A2),--(C2:C10="code"),D2:D10)
and it worked. thanks though.
 
i was just advised to use
=SUMPRODUCT(--(A2:A10=A2),--(C2:C10="code"),D2:D10)
and it worked. thanks though
 
Back
Top