displaying the correct value.

G

GAIDEN

I'm using the vlookup function to display the different types of work hours
for employees. But if there are multiple descriptions, the 1st result is the
only one I get.

A C D
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

the forumlas i'm using are
for salary: IF(VLOOKUP(A2,A:C,3,0)="REGSAL",VLOOKUP(A2,A:D,4,0),0)
for regular: IF(VLOOKUP(A2,A:C,3,0)="REGLAR",VLOOKUP(A2,A:D,4,0),0)
for ot: IF(VLOOKUP(A2,A:C,3,0)="OVTIME",VLOOKUP(A2,A:D,4,0),0)
for vacation: IF(VLOOKUP(A2,A:C,3,0)="VACTON",VLOOKUP(A2,A:D,4,0),0)

How do i get the correct values to display?
 
T

T. Valko

Maybe this:

=SUMPRODUCT(--(A2:A10=A2),--(C2:C10="code"),D2:D10)

Replace "code" with the appropriate code: REGSAL, OVTIME, REGLAR, VACTON.

Or, better yet, use a cell to hold the code:

A1 = some code like REGLAR

=SUMPRODUCT(--(A2:A10=A2),--(C2:C10=A1),D2:D10)
 
F

Fred Smith

To get the correct values to display, you need to enter the correct formula.

To get the correct formula, you need to tell us what you want displayed.
What do you mean by "correct values"? Also, when you post back, please
clarify your table. It's very difficult to tell which column your data is
in.

Regards,
Fred.
 
G

GAIDEN

it worked. thanks for the help

T. Valko said:
Maybe this:

=SUMPRODUCT(--(A2:A10=A2),--(C2:C10="code"),D2:D10)

Replace "code" with the appropriate code: REGSAL, OVTIME, REGLAR, VACTON.

Or, better yet, use a cell to hold the code:

A1 = some code like REGLAR

=SUMPRODUCT(--(A2:A10=A2),--(C2:C10=A1),D2:D10)
 
G

GAIDEN

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup and display 5

Top