Combining If, Vlookup in formula

H

HR_Payroll

I need a formula to do the following.

If A3 (hours worked) is greater than 40 then look for employee name (on
separate tab in workbook) and take the overtie pay rate and multipy the rate
x A3, if greater than 40, take the regular rate and multiply the rate x A3.

The formula I created will figure choose the right rate but will not lookup
the person out of the list but this department has 20 people so I need the
formula to look for the person's name and then multiply the rate of pay (reg
rate if under 40 and OT rate if overt 40) x the number of hours. I can't
remember how to name a range in a formula so the vlookup will work right.

Thank you.
 
S

Sean Timmons

=IF(A3>40,VLOOKUP(A2,Sheet2!A:C,2,0)*A3,VLOOKUP(A2,Sheet2!A:C,3,0)*A3)

This assumes your employee name is on A2 of the current worksheet, and in
column a of your search sheet. This also assumes Overtime rate is in column
B and regular rate is in column C of your search worksheet.

To create names, highlight your esired set of data and type in a name on the
top left (left of the formula bar)
 
S

Shane Devenshire

Your question isn't clear - you say if A3 is greater than 40 then and a
little further down you say if A3 is greate than then...? Both your
conditions are the same!

If you mean <=40 for the second condition then

=A3*VLOOKUP(A2,Sheet3!A1:C30,2+(A3>40),)


Where the name is in A2 and
 

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


Top