Using IF function

M

maryjayhawk

Greetings:

I have a worksheet (A) with a list of employee names in one column and
their rates of pay in another column.

On another worksheet (B) within the workbook, I have a list of hours
that employees worked, that includes a list of employee names in one
column and their hours in another column.

I want to reference the Worksheet A rates of pay in order to calculate
the value of the hours on Worksheet B.

I'd like to have a column on the worksheet B that says:

If the employee name on Worksheet B equals an employee name on
Worksheet A, return to this cell on Worksheet B the associated rate of
pay.

Example:

Worksheet A, cell C3 = Mary, cell E3 = $25.00
Worksheet A, cell C4 = Joe, cell E4 = $15.00
Worksheet A, cell C5 = Harry, cell E5 = $20.00

Worksheet B, cell D8 = Mary, cell H8 = formula cell

Formula should be: If Cell D8 equals one of cell C3-C5, then return
the matching E cell

I can make this work with one cell reference. (If D8 = C3, then E3)
But I'd like it to work with the whole list, so that the formula would
check the Worksheet B cell against the whole Worksheet A list.

Can someone please assist?

Many thanks,

Mary
 
M

Mikeopolo

This seems like a case where vlookup formula will work.

To make it easier, give your employee names and rates a range name, e
"emprates".

Then in the second worksheet, I'll assume the employee name is in cel
A1, and the hours in B1.

Then the value of the hours (in cell C1, say) becomes
=vlookup(a1,emprates,2,false)*b1

Then copy this formula down the entire column.

Note that the list of employees and rates must be sorted on th
employee name, and the employee name must be exactly the same in bot
worksheets, no spelling, punctuation or space differences at all.

Any errors will show up as #N/A, which are most likely due to spellin
differences on the name.

Regards
Mik
 
M

maryjayhawk

Many, many thanks, Mike.

That worked perfectly and beautifully.

Just out of curiousity, in the formula: =vlookup(a1, emprates,2,
false)*b1, what do the "2" and "false" refer to? (Just trying to make
sense of the logic employed...)

Thanks again. You really saved me a lot of time today and for many
days to come!

Mary
 
M

Mikeopolo

Hi Mary

Glad to hear it worked for you.

The '2' refers to column 2 of the lookup table, which contains th
rates.

'False' is what I usually use, but means that the table does not hav
to be sorted by the lookup value, whereas 'true' means that it does.
prefer false in case I forget to sort.

Regards
Mik
 

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

Top