Requesting Help

[Scooterdog]

My job has 2 pay scales. One is Conductor
& the other is Brakeman.
I need a formula with a table that will do
the following example:

If my pay scale is c and we handle 1 to 80 cars,
my basic pay would be $75.00. If we handle
81 to 105 cars, my basic pay would be $85.00.

Now, if my pay scale is b and we handle 1 to 80 cars,
my basic pay scale would be $65.00. If we handle
81 to 100 cars, my basic pay scale would be $75.00.

This would get me started. BUT, I need the formula
using a table.
Thank you in advance

 

[Frank Kabel]

Hi
try the following:
1. Create a lookup table on a separate sheet (e.g. called 'lookup')
with the following layout:
A B C ...
1 b c
2 1 65 75
2 81 75 85
.....

Now in your second sheet in A1 put your pay scale and in B1 the number
of sold cars and use the following formula in C1:
=INDEX('lookup'!$A$1:$D$20,MATCH(B1,'lookup'!$A$1:$A$20,1),MATCH(A1,'lo
okup'!$A$1:$D$1,0))

 

[Aladin Akyurek]

=A1*LOOKUP(A1,IF(B1="c",cTable,bTable))

where A1 houses the number cars.

cTable would look like:

1 75
81 85

bTable would look like:

1 65
81 75