| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying the correct value.
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
displaying the correct value.
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) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
displaying the correct value.
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. "GAIDEN" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
displaying the correct value.
it worked. thanks for the help
"T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
displaying the correct value.
i was just advised to use
=SUMPRODUCT(--(A2:A10=A2),--(C2:C10="code"),D2:D10) and it worked. thanks though "Fred Smith" wrote: 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. "GAIDEN" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
displaying the correct value.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... it worked. thanks for the help "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If with AND - Is this correct? | Excel Worksheet Functions | |||
| If correct, add 1 | Excel Worksheet Functions | |||
| Calculation not correct | Excel Discussion (Misc queries) | |||
| if(b2=1,b3,0) is this correct | Excel Worksheet Functions | |||
| Only using the correct cell for the sum | Excel Worksheet Functions | |||
Linear Mode
