complex nested IF statement

Hello,

I run a seasonal business and I keep track of mileage for pay in an Excel spreadsheet. The way it's set up is that I have a list of employees and the locations they travel to on Sheet1. Sheet2 then contains the formulas to calculate what I owe them. It's calculated with the following code:

Please Login or Register  to view this content.

In the above example, it's looking to see if an X has been placed in a cell (which indicates an employee did work at a specific location), and if so, it then checks to see the mileage (listed in AF3 on sheet2). The further away the job, the more they make, so if the location is 50 miles or less, they get $35, etc. I always send five employees to a location.

What I would like to do is expand on this code to have three separate pay levels (instead of just the one universal level right now). How can I recode the above? What I'd like to do is rather than use "x" on sheet 1, I would instead use 1, 2, or 3 indicating which level they should be paid. Level 1 employees would now make $30 if the location is between 0 and 50 miles away. Level 2 employees would make $35, level 3 employees would make $40. This trend would continue for each mileage tier.

I'm not sure of the best way to go about it as I'm looking for concise code, so I thought I would turn to the experts for help! Feel free to ask any follow up questions if any of this is unclear. Thanks in advance!

Note - I use Excel 2003. I would consider myself higher than a beginning user but certainly far from advanced. The code generated above was a combination of my research and help from other sources (including this forum!)

I'll let you figure out the full implementation, but the way you could adapt your LOOKUP() formula to use a bunch of "return" value arrays is like so...

1) AF3 = the miles they drove
2) AG3 = a number from 1-3 to represent which tier of values to return.
3) Use this formula:

=LOOKUP(AF3, {0,51,101,151}, CHOOSE(AG3, {35,45,60,70},{40,50,65,75},{50,60,70,80}))