Hourly/Salaried employees & VLOOKUP

[P.Rumpz]

Hello all,

I want to compare my Sheet1 data to Table1 in order to calculat
insurance premiums, which are computed as a percentage of employee'
salary.

In 'Sheet1', col. A is SSN, col. B is Name, col. C is Salary
Currently, to populate the Salary column I am using:

=VLOOKUP(A3,Table1!$G$3:$H$16,2,0)
*Where Table1 is two columns, col. G is SSN, col. H is Rate


Some employees are hourly and some are salaried. There is a column A
in a 'Sheet 2' which determines this (SAL vs HR).

Is it possible to augment the VLOOKUP function so if the employee i
salaried, simply return the Rate from Table1 to Sheet1,Col.C - and i
the employee is hourly, multiply the Rate from Table1 by 2080 (40*52
and return a calculated Rate to Sheet1, Col.C

Sorry for being so long-winded. This one has me stumped to the max!
am not even sure that VLOOKUP is the best way to do this.....and I a
not macro smart either.

TYIA,

Pa

 

[Vasant Nanavati]

=IF(Sheet2!AU3="SAL",VLOOKUP(A3,Table1!$G$3:$H$16,2,0),VLOOKUP(A3,Table1!$G$
3:$H$16,2,0)/2080)

Assumes that the employee name lists in Sheet1 and Sheet2 are identical. If
not, you would have to do a double VLOOKUP.

 

[Frank Kabel]

Hi
try
=VLOOKUP(A3,Table1!$G$3:$H$16,2,0)*IF(AU3="SAL",2080,1)

 

[P.Rumpz]

Excellent. It worked like a charm.


Thanks!

Pa