mileage cap using vlookup
 

Please help I think this is fairly easy but i'm kinda in a hurry, Thanks.
"I'm not sure Vlookup is the function I want.

The cap is as follows.

$100. - $199. = up to 100 miles allowed, (less is ok we do not move them up.)
$200 - $399. = up to 200 miles allowed.
$400 - $599 = up to 300 miles allowed
$600 - $799 = up to 400 miles
$800 - $999 = 500 miles
I will pull the payroll amount from sheets 9501, 9502, 9503, etc. cell "G48"
On the Employee sheet there is a cell "E30" which has the formula ='9501'!E2
which is where all of 9501's mileage is pulled from for the week. "E31" is
the cell for "9502" Again if the mileage is within range we just leave it
alone.

mileage cap using vlookup
 

A4 would be the entered milage

=IF(A4<200,"LESS",IF(A4399,"MORE","OK"))

Just repeat the formula and change the amounts
OR if the amounts are entered in a cell, say $200 is in cell B1, then
replace 200 with $B$1 - which is the better option, then the vakues can be
changed without changing the formulae.
You can also change the "ANSWERS" in the formula to numbers, or whatever
suits your need.



"Tomkat743" wrote:

Please help I think this is fairly easy but i'm kinda in a hurry, Thanks.
"I'm not sure Vlookup is the function I want.

The cap is as follows.

$100. - $199. = up to 100 miles allowed, (less is ok we do not move them up.)
$200 - $399. = up to 200 miles allowed.
$400 - $599 = up to 300 miles allowed
$600 - $799 = up to 400 miles
$800 - $999 = 500 miles
I will pull the payroll amount from sheets 9501, 9502, 9503, etc. cell "G48"
On the Employee sheet there is a cell "E30" which has the formula ='9501'!E2
which is where all of 9501's mileage is pulled from for the week. "E31" is
the cell for "9502" Again if the mileage is within range we just leave it
alone.

mileage cap using vlookup
 

=if(or(e3="",e3=0),"",CHOOSE(TRUNC(E3/200)+1,100,200,300,400,500))

--
Regards,
Tom Ogilvy


"Tomkat743" wrote:

Please help I think this is fairly easy but i'm kinda in a hurry, Thanks.
"I'm not sure Vlookup is the function I want.

The cap is as follows.

$100. - $199. = up to 100 miles allowed, (less is ok we do not move them up.)
$200 - $399. = up to 200 miles allowed.
$400 - $599 = up to 300 miles allowed
$600 - $799 = up to 400 miles
$800 - $999 = 500 miles
I will pull the payroll amount from sheets 9501, 9502, 9503, etc. cell "G48"
On the Employee sheet there is a cell "E30" which has the formula ='9501'!E2
which is where all of 9501's mileage is pulled from for the week. "E31" is
the cell for "9502" Again if the mileage is within range we just leave it
alone.