Select from List of names to equal a specific rate

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)

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

"Shirley" <Shirley@discussions.microsoft.com> wrote in message
news:424E17A5-200D-4987-A125-FDBAF8ABC20E@microsoft.com...
>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)
>
> 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
>
> "Shirley" <Shirley@discussions.microsoft.com> wrote in message
> news:424E17A5-200D-4987-A125-FDBAF8ABC20E@microsoft.com...
> >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.