<<Requesting Formula>>

[Scooterdog]

On my job I have "two" different pay scales. One is for conductor
rate, and one is for helper rate.
I need a formula that will allow me to do the following:
A B C D E
1 Pay Rate Earned Car Rate Cond Pay Rate Helper Rate
2 c 1 to 50 $50.00 $45.00
3 35 51 to 100 $75.00 $50.00
If I put the letter "c" in a2 and the car rate 35 in a3, I want the
answer of $50.00 to come up for the pay rate in b2.

If I put the letter "b" in a2 and the car rate 35 in a3, I want the
answer of $45.00 to come up for the pay rate in b2.

I understand the formula "vlookup"; but I do not know how to combine
the requirements of a1 plus a2 to get a answer. I know a table
of pay rates will be needed. Just trying to keep this simple.
Thank you in advance!

 

[Biff]

Hi!

The best approach would be to build a table and use a
vlookup/match formula if you have more variables then you
have posted in your example data.

If you only have the few variables as you have posted, try
this:

=IF(A2="C",LOOKUP(A3,{1,50;51,75}),IF(A2="B",LOOKUP(A3,
{1,45;51,50}),0))

Biff

 

[Scooterdog]

Biff, I typed your formula in but only got a error message each time.
I even tried to copy and paste from this reply to excel. When I
pressed the enter key, the formula itself showed in the cell.
Any ideas? Thank you.

 

[Biff]

Hi!

What type of error message are you getting? Is it the general "The formula
you have entered contains errors" or is it something specific like #VALUE! ?
If it's the general error message then you may have typed it in wrong?

As far as the copy/paste and the formula itself being displayed means that
the formula is being interpreted as a text string. Check the formula bar and
look for a leading apostrophie. If you see it, delete it. It's kind of hard
to troubleshoot formulas without seeing them.

That formula does work. I tested it. If you can't get it straightened out I
can send you a sample file that shows how it works.

Biff

 

[Scooterdog]

Biff, I found out what I was doing wrong this morning. I had to
change the format of the cell from "general" to "text". DUHHHH!!
Thanks for your patience and replys...