VLookup

[Guest]

I am trying to create a formula that will calculate a rate of pay according to a code. I want to have a column that is labeled "code" and one that is labeled "Straight Time" and one labeled "Over Time". I want to be able to put in a code such as "MA" and the right rate of pay Straight Time or Over Time will fill in for the correct code. Each code is unique and each rate of pay is unique. Any help would be great. I have tried a few things such as making another table with the codes and rates and using Vlookup, but something is not working right! Thanks in advance!

Jessica

 

[AlfD]

Hi!

It may be something simple like:

=if(D1="MA",E1=6.25, if (D1="GG",8.45,""))

Depends on how many pay rates you want to define (I've just used 2: M
and GG, giving rates of 6.25 and 8.45 units of currency).

But more information on your inputs, outputs and column structure woul
be helpful.

Al

 

[Debra Dalgleish]

Create a lookup table, with the codes, and rates of pay, e.g.:

Code ST OT
MA 24 30
FL 20 26

Name this table (RateList in this example). There are instructions here
for naming a range:

http://www.contextures.com/xlNames01.html

In your formula, refer to this table to extract the rate. For example:

=VLOOKUP($F2,RateList,2,0)

will return the straight time rate, where cell F2 contains a code.

 

[nowfal]

Mr.Alf D,
If the rates is not constant, i want to use between i.e. 0.650 t
0.700, what will be the formula
thx
nowfa

 

[AlfD]

Hi Nowfal!

I seem to be getting mixed messages from Jessica and yourself.

So I don't know really what your enquiry is.

Al

 

[nowfal]

Hi Alf,
I am doing a spread sheet, in which one sheet produces bills
for that i am using the other sheet as a price list having 2
currencies (both purchase and sale rates) for example if i put USDOLLA
i will get the rate of USD from the other sheet which is variable
mean between .380 and .386. So, when i read jessica's posting, its m
problem too, with some more complication.
Still if it is not clear i would like to post the entir
worksheet .
thanks
nowfa