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


--
maryjayhawk
------------------------------------------------------------------------
maryjayhawk's Profile: http://www.excelforum.com/member.php...o&userid=32964
View this thread: http://www.excelforum.com/showthread...hreadid=527904

This seems like a case where vlookup formula will work.

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

Then in the second worksheet, I'll assume the employee name is in cell
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 the
employee name, and the employee name must be exactly the same in both
worksheets, no spelling, punctuation or space differences at all.

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

Regards
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=527904

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


--
maryjayhawk
------------------------------------------------------------------------
maryjayhawk's Profile: http://www.excelforum.com/member.php...o&userid=32964
View this thread: http://www.excelforum.com/showthread...hreadid=527904

Hi Mary

Glad to hear it worked for you.

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

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

Regards
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=527904