IF and LOOKUP statements

[lmmay]

I have a workbook with a Labor/Equipment sheet. The sheet has dropdown menus for Employee, Title, Status (FT, PT, T) and Equipment. Labor costs are calculated based on work categories (A, B, C – G).
Full time employee regular labor costs are not calculated for categories C – G. Part Time and Temporary employees’ costs are calculated for all categories.
The employee name, title, rate & status (column C) is listed in the payroll record.
This is the formula I am using to calculate the cost for regular time hours.
=IF(AND('PAYROLL RECORD'!$L$6="A",C1>="P"),C3*N3,0)+IF(AND('PAYROLL RECORD'!$L$6="B",$C1>="P"),(C3*N3),0)+IF('PAYROLL RECORD'!$L$6>="C",C3*N3
My problem is the formula can not be copied to each employee block since the employee in the payroll record is in rows 1 through xx and copying changes the C1 (Payroll Record) to the corresponding employee row in this sheet, not C2, C3, etc.
Can the formula be modified to look up the employee for status rather than the column C row?

A

B

C

D

E

F

G

H

I

J

K

L

M

N

OT1

P

Q

1

Employee/

RATE w/

COST

RATE

1/3

1/4

1/5

1/6

1/7

1/8

1/9

REG

OT1

OT2

REG TIME COST

2

Equipment

FRINGE

CODE

Sun

Mon

Tue

Wed

Thu

Fri

Sat

3

NAME

REG

0.0

4

EQUIP

0.0

$ -

5

EQUIP

0.0

$ -

6

EQUIP

0.0

$ -

7

EQUIP

0.0

$ -

8

NAME

REG

0.0

9

EQUIP

0.0

$ -

10

EQUIP

0.0

$ -

11

EQUIP

0.0

$ -

12

EQUIP

0.0

$ -

<tbody>
</tbody>

 

[ekrause]

Re: Need Help with IF and LOOKUP statements

Assuming you have a list of Employees with their Labor Status (Full-Time & Part-Time) listed side by side, you can use an index-match formula or a vlookup formula depending on which you would be more comfortable with. Assuming your table looks like a mock up I created below, you can use the following formulas & just copy over C1 in your formula. You will have to do some manipulation on the ranges & cell locations. I did my best to explain where everything was using mock ups. Let me know if you have any follow up questions.

Sheet2 Tab:

[TABLE="width: 1000"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Employee 2[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD]=INDEX('PAYROLL RECORD'!$B$2:$B$12,MATCH(Sheet2!$B4,'PAYROLL RECORD'!$A$2:$A$12,0))[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Employee 4[/TD]
[TD]PT[/TD]
[TD][/TD]
[TD]=VLOOKUP($B6,'PAYROLL RECORD'!$A$2:$B$12,2,0)[/TD]
[/TR]
</tbody>[/TABLE]


PAYROLL RECORD Tab:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]FT/PT[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Employee 1[/TD]
[TD]FT[/TD]
[TD]14.92[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Employee 2[/TD]
[TD]FT[/TD]
[TD]12.15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Employee 3[/TD]
[TD]PT[/TD]
[TD]11.71[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Employee 4[/TD]
[TD]PT[/TD]
[TD]14.50[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Employee 5[/TD]
[TD]FT[/TD]
[TD]15.73[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Employee 6[/TD]
[TD]PT[/TD]
[TD]13.09[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Employee 7[/TD]
[TD]FT[/TD]
[TD]15.26[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Employee 8[/TD]
[TD]FT[/TD]
[TD]16.21[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Employee 9[/TD]
[TD]PT[/TD]
[TD]12.00[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Employee 10[/TD]
[TD]FT[/TD]
[TD]15.19[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Employee 11[/TD]
[TD]FT[/TD]
[TD]17.77[/TD]
[/TR]
</tbody>[/TABLE]

Repost for table legibility and timed out on editing

 

[lmmay]

Re: Need Help with IF and LOOKUP statements

I am a bit confused how that will look in my formula. Could you please show me the VLOOKUP in my formula?
Thank You

 

[ekrause]

Re: Need Help with IF and LOOKUP statements

From this:

=IF(AND('PAYROLL RECORD'!$L$6="A",C1>="P"),C3*N3,0)+IF(AND('PAYROLL RECORD'!$L$6="B",$C1>="P"),(C3*N3),0)+IF('PAYROLL RECORD'!$L$6>="C",C3*N3

to this:

=IF(AND('PAYROLL RECORD'!$L$6="A",VLOOKUP($B6,'PAYROLL RECORD'!$A$2:$B$12,2,0)>="P"),C3*N3,0)+IF(AND('PAYROLL RECORD'!$L$6="B",$VLOOKUP($B6,'PAYROLL RECORD'!$A$2:$B$12,2,0)>="P"),(C3*N3),0)+IF('PAYROLL RECORD'!$L$6>="C",C3*N3

 

[lmmay]

Re: Need Help with IF and LOOKUP statements

Excellent ! Works perfectly ! However, on the Labor-Equip tab, there are 7 blocks to record the employee and equipment. There are occasions when only 1 or 2 of the blocks are used. The formula returns a #N/A in the cost cell.
Can I nest an IF statement in the formula to ignore the empty employee cell?

Thanks

 

[ekrause]

Re: Need Help with IF and LOOKUP statements

Yes, you can do this pretty easily depending on your version of Excel. This formula should do what you want (changes made in red). If this doesn't work, I can write you a different formula that will work on older versions of Excel.

=iferror(IF(AND('PAYROLL RECORD'!$L$6="A",VLOOKUP($B6,'PAYROLL RECORD'!$A$2:$B$12,2,0)>="P"),C3*N3,0)+IF(AND('PAYROLL RECORD'!$L$6="B",$VLOOKUP($B6,'PAYROLL RECORD'!$A$2:$B$12,2,0)>="P"),(C3*N3),0)+IF('PAYROLL RECORD'!$L$6>="C",C3*N3,"")

 

[lmmay]

Re: Need Help with IF and LOOKUP statements

Thanks much.