Select from List of names to equal a specific rate

S

Shirley

I have a Excel column which includes list of last names. I would like to
assisgn a distinctive numeric rate (in preceeding separate column) to each
last name. An example would be: Smith = 10.0; Underwood = 9.50; Ace = 5.75.
Then I will multiply the rate by hours worked (I can handle that part).
Thanks!
 
B

Biff

Hi!

It sounds like you probably want some type of lookup table/formula but you
need to provide more details.

The table would look like this:

Smith.............10.0
Underwood.....9.5
Ace...............5.75

So, now you have a table but what do you want to do with it? If you want to
use it to calculate pay, you might do something like this:

Total_Hours*VLOOKUP(A1,Sheet2!A1:B3,2,0)

Where:

A1 = name
Sheet2!A1:B3 = location of the lookup table
2 = the column number of the lookup table to return the value from
0 = means the lookup table is not sorted because you only want an exact
match

Biff
 
S

Shirley

As I have been reading, to use a lookup table I need to sort in ascending
order? I guess that can be on another worksheet? Thanks, I'll keep messing
with it. Yes, I am trying to calculate a billable rate. I have created a
custom Business Works report to Excel & just need a few more fixes to insert
info I'd like to show.
 
R

Roger Govier

Hi Shirley

No your lookup table does not have to be sorted.
As Biff said, use the 4th argument in Vlookup of False or 0 to deal with
tables in an unsorted order
=Total_Hours*VLOOKUP(A1,Sheet2!A1:B3,2,0)
Click to expand...

or exactly the same meaning
=Total_Hours*VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)

Regards

Roger Govier
 
Top