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!
--
Shirley

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)

Whe

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

"Shirley" wrote in message
...
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!
--
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.
--
Shirley


"Biff" wrote:

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)

Whe

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

"Shirley" wrote in message
...
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!
--
Shirley

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)

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

Regards

Roger Govier


Shirley wrote:
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.